Vlookup - Using multiple criteria to search for - possible ?

[springbrook]

I am reasonable familiar with Vlookup, however can I have the Vlookup formula search for more than 1 criteria ?

Example:
.....Vlookup(a1,c5,d4,f10,TABLES!AQ5:BB86,10)

Please help,
Thanking you in advance
Springbrook

 

[rgrovier]

Springbrook,

I'm not clear on what you are trying to do here ...

are you trying to concatenate a1, c5, d4, and f10 and then find that in AQ and return the value from the 10th column - or -

are you trying to find a1, c5, d4, and f10 in AQ and return multiple values - or -

do you know that only one of the four (a1, c5, d4, and f10) is in AQ and you are trying to return the one that is valid?

1st is easy, 2nd and 3rd ... I'd have to noodle awhile ... a long while.

 

[venkat1926]

Quote
....Vlookup(a1,c5,d4,f10,TABLES!AQ5:BB86,10)
Unquote

Try this--- not tested

In an empty column for e.g. a100 to a103 type
a1
c5
d4
f10

in the next column i.e. b100 type
=vlookup(indirect(a100),tables!AQ5:BB86,10)
Copy his formula down up to b103
venkat

 

[springbrook]

On 1 sheet I have criteria in certain cells.
On another sheet I have a sorted list that contains the data that matches the cells on sheet 1.

I want the Vlookup formula to find each cell from sheet 1 that contains the criteria.......to find these cells from the list on the other sheet then look ap the corresponding value in one of the cells next to the found data and return that value.

 

[Yogi Anand]

Hi Springbook:

Please post a few rows of your data, your criteria, and your expected result -- so we can se what exactly you are working with.

 

[springbrook]

Sheet 1:
H9=20,J9=10,E12=3050,T9=2,Q9=13

Sheet 2 has the follwing corresponding row:
A1=20,A2=10,A3=3050,A4=2,A5=13 and A6="40x1.5"

I want the Vlookup formula to find this corresponding row after matching all the above criteria as on sheet 1 and then return the value in A6 from sheet 2.

 

[West Man]

You mention the data is a corresponding row on sheet 2 but the cells you reference indicate a column. I would guess that the data is indeed in a row. You will need to clarify in order to get an appropriate answer. Others can likely answer better and more quickly, but if the data is in 6 columns, sumproduct combined with match and index comes to mind.

 

[springbrook]

Sorry, my example was indeed indicating a column rather than a row (long day yesterday).
I have since rehashed my example (below) to show the data in a row.

Sheet 1:
H9=20,J9=10,E12=3050,T9=2,Q9=13

Sheet 2 has the follwing corresponding row:
A1=20,B1=10,C1=3050,D1=2,E1=13 and H1="40x1.5"

I want the Vlookup formula to find this corresponding row after matching all the above criteria as on sheet 1 and then return the value in A6 from sheet 2.

Regards
Springbrook

 

[West Man]

Try in a cell on sheet 1
=INDEX(Sheet2!E1:E12,SUMPRODUCT(--(Sheet2!A1:A12=H9),--(Sheet2!B1:B12=J9),--(Sheet2!C1:C12=Sheet1!E12),ROW(1:12)),1)

Adjust ranges in sheet 2 as needed and adjust the 12 in ROW to match the number of rows in your table.

 

[West Man]

My apologies. In my haste to assist you, I see that I included only the first 3 matching criteria in my formula. You can likely see how to include the last 2. If you have problems, post back and I will try to assist you.