Lookup for products in two columns

[colbyclay]

Hi all,
I am trying to see what products we have in SAP based on their names. I have the common product names in column A, the SAP codes in column B and the SAP name in Column C. What I would like to do is find a way to pull the SAP codes associated with the common name. For instance, if we have "acetic acid" in column A, I need it to look for all instances in column C that include "acetic acid" (i.e. BA.ACETIC ACID -200 LTR DRUM, BA.ACETIC ACID -25 KG CAN, BA.ACETIC ACID - 55 GAL DRUM) and give me the SAP code numbers from column B.

I tried to use the example found here: http://www.excelforum.com/excel-prog...atic-data.html by it is not working.

I have attached an example spreadsheet. Can anyone help?

Thanks


EXAMPLE IS HERE: example.xls

[Domenic]

It's not clear where you'd like the results returned. How about something like this? First, let G2:L2 contains "widget", "spring", etc. Then try the following formulas...

G3, copied across:

=COUNTIF($C$2:$C$14,"*"&G$2&"*")

G4, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(G$4:G4)<=G$3,INDEX($C$2:$C$14,SMALL(IF(ISNUMBER(SEARCH(G$2,$C$2:$C$14)),ROW($C$2:$C$14)-ROW($C$2)+1),ROWS(G$4:G4))),"")

[colbyclay]

Hi Domenic,
That is about 95% of the way there, thank you so much. The issue that I have is that our SAP data entry can be sloppy sometimes. For instance, instead of spelling SPRING, it could be spelled SRPING, or with the Elitebook. There could be spacing issues where it is Elite book, elite.book, etc. Is there a way to change the countif statement to account for that.

Also, and I know this is asking a lot: Can you explain the G4 array?

Thanks

[Domenic]

Try to search this Board or Google for "fuzzy lookup"...

[colbyclay]

That is where I get stuck. I cannot figure out how to build the fuzzy and then the logic to extract what I need.

[colbyclay]

example2.xlsexample2.xls

Ok, Fuzzy is going to take more delving into. For now, I have it working somewhat, but how do I get it to give me multiple SAP numbers for the same product? In the example attached, it will give me the lowest numbered SAP number for products, but I need it to give me all the SAP numbers. What do I need to put in the cell next to it?

[Domenic]

Attachment 148438Attachment 148438

Ok, Fuzzy is going to take more delving into. For now, I have it working somewhat, but how do I get it to give me multiple SAP numbers for the same product? In the example attached, it will give me the lowest numbered SAP number for products, but I need it to give me all the SAP numbers. What do I need to put in the cell next to it?

Try...

G2, copied down:

=COUNTIF($C$2:$C$3133,"*"&A2&"*")

H2, copied across and down:

=IF(COLUMNS($H2:H2)<=$G2,INDEX($B$2:$B$3133,SMALL(IF(ISNUMBER(SEARCH($A2,$C$2:$C$3133)),ROW($C$2:$C$3133)-ROW($C$2)+1),COLUMNS($H2:H2))),"")

Note that if you'd like them listed in reverse order, replace SMALL with LARGE.

Sample workbook attached.