Multiple Lookup Criteria - Help Required

[satish gubbi]

I have two tabs in workbook

1. Vendors Found
2. Consolidated List

I need a formulae which looks for "vendors found values" in "consolidated List" if found place the corresponding column values in Consolidated list tab.

Vendors name will be found anywhere in the Merchant description tab.

Your help in this regard is much appreciated.

[mana]

C2:　=LOOKUP(0,0/FIND('Vendors Found'!$A$1:$A$21,ConsolidatedList!A2),'Vendors Found'!$B$1:$B$21)

D2:　=LOOKUP(0,0/FIND('Vendors Found'!$A$1:$A$21,ConsolidatedList!A2),'Vendors Found'!$C$1:$C$21)

[mana]

1)define name
name;
List_Merchant
refers to :
=OFFSET('Vendors Found'!$A$1,0,0,COUNTA('Vendors Found'!$A:$A),1)

2)formula of C2
=IFERROR(LOOKUP(0,0/FIND(List_Merchant,A2),OFFSET(List_Merchant,,1)),"")

3)formula of D2
=IFERROR(LOOKUP(0,0/FIND(List_Merchant,A2),OFFSET(List_Merchant,,2)),"")

4)Fill it down

[satish gubbi]

C2:　=LOOKUP(0,0/FIND('Vendors Found'!$A$1:$A$21,ConsolidatedList!A2),'Vendors Found'!$B$1:$B$21)

D2:　=LOOKUP(0,0/FIND('Vendors Found'!$A$1:$A$21,ConsolidatedList!A2),'Vendors Found'!$C$1:$C$21)

This formula working as expected, thank you very much Mana.

[satish gubbi]

1)define name
name;
List_Merchant
refers to :
=OFFSET('Vendors Found'!$A$1,0,0,COUNTA('Vendors Found'!$A:$A),1)

2)formula of C2
=IFERROR(LOOKUP(0,0/FIND(List_Merchant,A2),OFFSET(List_Merchant,,1)),"")

3)formula of D2
=IFERROR(LOOKUP(0,0/FIND(List_Merchant,A2),OFFSET(List_Merchant,,2)),"")

4)Fill it down

I will use this as well and keep you posted with the updates. Thank you very much again.