0

I have 2 sheets.

Sheet 1 is the work sheet:

SONY KDL55W805CBAEP LED TV (Flat, 55 Zoll, FullHD, 3D, SMART TV, Android TV)
SONY KDL75W855CBAEP LED TV (Flat, 75 Zoll, FullHD, 3D, SMART TV, Android TV)
SONY KDL48W705CBAEP LED TV (Flat, 48 Zoll, FullHD, SMART TV)
SONY KD43X8305CBAEP LED TV (Flat, 43 Zoll, UHD 4K, SMART TV, Android TV)
SONY KDL50W756C LED TV (Flat, 50 Zoll, FullHD, SMART TV, Android TV)

Sheet 2 contains the full model reference numbers:

KDL55W805CBAEP
KDL75W855CBAEP
KDL48W705CBAEP
KD43X8305CBAEP
KDL50W756CBAEP

I'm using =IFERROR(LOOKUP(2^15;SEARCH(Sheet2!$B$1:$B$114;Sheet1![Row #]),Sheet2!$B$1:$B$114);"")

Everything gets match as long as the text in Sheet 1 is a 100% match. But as soon as it isn't I don't get a match.

For example.

Sheet 1:

SONY KDL50W756C LED TV (Flat, 50 Zoll, FullHD, SMART TV, Android TV)

Sheet2:

KDL50W756CBAEP

How can I make those match aswell?

EDIT:

This is sheet 1:

Sheet1

This is sheet 2:

So in column item_model I want the full model reference. But as not all references in Sheet 1 match 100% with Sheet 2 I have a problem

Sheet2

Improve this question
4
  • are those separate columns or all in one column? Commented May 12, 2016 at 20:22
  • apparently its all in one column...item_2_name Commented May 12, 2016 at 20:55
  • Now that we know that the description is all in one cell, is the model ref always the second "word" in string on sheet1? If not then this will need to be in vba. Commented May 12, 2016 at 20:58
  • 1
    @findwindow the master has arrived! Commented May 12, 2016 at 20:59

2 Answers 2

Reset to default
3

Use this formula if the Model number is always the second "Word":

=IFERROR(INDEX(Sheet2!B:B,MATCH(TRIM(MID("*" & SUBSTITUTE(B2," ",REPT(" ",99)),99,99)) & "*",Sheet2!B:B,0)),"")

enter image description here

EDIT: In Dutch, I think

=ALS.FOUT(INDEX(Sheet2!B:B;VERGELIJKEN(SPATIES.WISSEN(MIDDEN("*" & SUBSTITUEREN(B2;" ";HERHALING(" ";99));99;99)) & "*";Sheet2!B:B;0));"")

I may have some typos or missing )

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

IFERROR, INDEX, MATCH, TRIM, MID, SUBSTITUTE, REPT are the excel function that would need translating to your local dialect. Also look at potentially changing a coma (,) to a semi colon (;) depending on what your system uses for separators.
I'm using 2016 in Dutch :( I'm just installing the English language pack
I am a Canuck and unfortunately I do not know "Hollish" as my cousin once said! Now if you need it in french I MIGHT be able to help you. 8) You should not have any issues with 2016.
@Wouter what are the actual names of the sheets? You will need to replace Sheet2 with the name of the sheet that you are showing as "Sheet 2"
@ForwardEd google is your friend "English to Dutch Excel formula" and I found a cool list.
|
2 
=IFERROR(INDEX($B$1:$B$6,IF(AGGREGATE(14,6,(ISNUMBER(SEARCH(C1,$A$1:$A$6))*ROW(A1:A6)),1),AGGREGATE(14,6,(ISNUMBER(SEARCH(C1,$A$1:$A$6))*ROW(A1:A6)),1),-1)),"not found")

You will have to update the ranges to suit your needs.

C1 what you are looking for.
First A1:A5 what you are looking in.
B1:B6 what you want to return.
Row(A1:A5) is acting as a counter and may need adjusting if your data is not in the first row.

Tossed in some error generation and checking in case the item you were looking for is not in the list.

proof of concept

This will work as long as what you are searching for is not so short as to be found partially after the start in the string. if you loosk at my example if you searked for K it would toss in the result from row 6 because K is found within the text in A6

In light of the edits

=IFERROR(INDEX($B$1:$B$6,IF(AGGREGATE(14,6,(ISNUMBER(SEARCH(MID(C1,FIND(" ",C1)+1,FIND(" ",C1,FIND(" ",C1)+1)-FIND(" ",C1)-1),$A$1:$A$6))*ROW(A1:A6)),1),AGGREGATE(14,6,(ISNUMBER(SEARCH(MID(C1,FIND(" ",C1)+1,FIND(" ",C1,FIND(" ",C1)+1)-FIND(" ",C1)-1),$A$1:$A$6))*ROW(A1:A6)),1),-1)),"not found")

This assumes what you are searching for is always between the first and second space in the item name.

Improve this answer

17 Comments

If you build it, he will come...If you get it wrong, the master will show you the error of your ways.
I've edited my question as I think we misunderstood each other :( Sheet 1 is the first tab of the excel file and sheet2 the second tab of the file
that part I got, and why I said you would have to edit the cell references to suit your data. The problem is, I thought SONY, KDL..., LED, TV, etc were all in separate columns. The problem now is we need to strip out the part you are looking up from the rest of the text in the column
@findwindow I accidently sniped an accepted answer from scott earlier today with a really bad answer. I made it overly verbose! I eventually posted to the OP to unaccept my answer and give it to scott. Was kind of funny
I think that is only because Scott was slow with the post. I think he has the better more concise answer...one of these days I will wrap my head around that add a huge space and pull out the word separateing the spaces technique. Scott and Jeeped like that technique
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.