Vlookup - Using multiple criteria to search for - possible ?

springbrook

Board Regular
Joined
Feb 5, 2005
Messages
85
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

rgrovier

New Member
Joined
Sep 13, 2006
Messages
15
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Board Regular
Joined
Feb 5, 2005
Messages
85
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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

Board Regular
Joined
Feb 5, 2005
Messages
85
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

Well-known Member
Joined
Mar 27, 2006
Messages
1,175

ADVERTISEMENT

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

Board Regular
Joined
Feb 5, 2005
Messages
85
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

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
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

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
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.
 

Forum statistics

Threads
1,136,309
Messages
5,674,998
Members
419,541
Latest member
freddyboots

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top