PDA

View Full Version : [SOLVED] Multiple Lookup Criteria - Help Required



satish gubbi
11-08-2016, 11:00 PM
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
11-09-2016, 06:13 AM
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
11-09-2016, 06:36 AM
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
11-09-2016, 11:06 PM
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
11-09-2016, 11:07 PM
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.