Vlookup - Using multiple criteria to search for - possible ?

springbrook

Board Regular
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)

Springbrook

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

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

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.

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.

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.

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.

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

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.

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.

Replies
3
Views
297
Replies
7
Views
360
Replies
3
Views
257
Replies
6
Views
199
Replies
4
Views
223

1,220,011
Messages
6,151,449
Members
451,028
Latest member
greekness1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back