VLOOKUP using text in multiple cells as lookup value?

[senciser]

Hi everybody!

I am looking for a way to do a VLOOKUP or something similar using the text in 3 cells as search criteria. Concatenating these cells probably would not work as the words in the name don't necessarily follow the same order.
Please see file in attachment. (This is a very simplified version, the real workbook is far more complex with hundreds of products.)

On Sheet 2, I have a list of items; item code on column A, item name on column B and price on column C.
On Sheet 1, I want to create a price table for different items. Is there anyway to search the text on cells A1, B3, A4 through column B of Sheet2 and return the price for the item on B4? (In this case it would be item on row 2 of Sheet2)

Any help will be highly appreciated!!

[:) Sixthsense :)]

In B4 cell - Array Formula - Requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

Drag it down and right.

If you would like to include the A1 cell also as one of the criteria then use the below formula Array Formula - Requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

Adapt the cell references to your data range.

Hope this helps!

[FDibbins]

looking at you're data, a few questions...

i presume that on sheet1
A1 is this part...GB/T5781Hex Bolt FT CL4.8 M5-0.8X10PL
B3 is this part...GB/T5781Hex Bolt FT CL4.8 M5-0.8X10PL
but is A4 this part?...GB/T5781Hex Bolt FT CL4.8 M5-0.8X10PL

also, why do you have 2 12's in column A?

edit: is the data in A1, B3 and A4 always in the same place in the name on sheet2?

[:) Sixthsense :)]

Please Login or Register  to view this content.

In the above formula forgot to Protect the range Sheet2!B2:B15 with dollar symbols. So change it to Sheet2!$B$2:$B$15

[:) Sixthsense :)]

Please Login or Register  to view this content.

In the above formula forgot to Protect the range Sheet2!B2:B15 with dollar symbols. So change it to Sheet2!$B$2:$B$15

[FDibbins]

see if the attached is what you need?

[senciser]

Brilliant! Thank you so much Sixthsense!!

I changed a bit the formula in order to add A1 as one of the criterias and this seems to be working perfectly for me:
=IFERROR(INDEX(Sheet2!$C$2:$C$15,MATCH(1,(ISNUMBER(SEARCH(B$3,Sheet2!$B$2:$B$15)))*(ISNUMBER(SEARCH($A4,Sheet2!$B$2:$B$15)))*(ISNUMBER(SEARCH($A$1,Sheet2!$B$2:$B$15))),0)),"")


@FDibbins
The 2 12's in column A was a mistake on my part, sorry for the confusion!
A1 = Hex Bolt FT CL4.8, B3 = M5, A4 = 10, so the formula would search for a cell which contains ALL three words, in this case it would be B2 on Sheet2.


Thanks again for both of you! You saved me many hours of repetitive work

[senciser]

Oh, just saw your second post FDibbins, that's an interesting alternative! Thank you!

I'm putting this issue as solved.

[Winon]

@ FDibbins,

That was well done!

[FDibbins]

happy to help, and thanks for the rep