Multiple Criteria In Vlookup

[Dazzawm]

Hi. I know how to do Vlookup (using the wizard) when only having to look at one column. How do you or is it possible to look at more than one? For example I have 2 sheets and I need a result from one column by looking to see if the criteria is the same in 4 other columns first.

So I need columns B, D, E, & F on sheet 1 to look at the same columns on sheet 2 and if the data matches put what is in column C on sheet 2 into column C on sheet 1. Understand!!?? Thanks.

 

[Aladin Akyurek]

The same as yours just changed the column letters. I dont have it now as it didnt work.

Post the formula as you implemented it and also, did you apply control+shift+enter?

 

[Dazzawm]

Post the formula as you implemented it and also, did you apply control+shift+enter?

Like I said I have it no more and I did do ctrl+sh+ent. Shall we start again with the exact columns and a couple of extra ones!

The result needs to start in C2 on sheet 1 and needs to look at columns H, I, M, O, P, Q, R and T. If all the data matches in those columns on both sheets then I need what is in column C on sheet 2 put in column C on sheet 1.

The sheets are approx 40 columns wide by 31,000 rows.

 

[Matty]

Try like this...

C2, Sheet1:

=INDEX(Sheet2!C$2:C$100,MATCH(1,IF(Sheet2!H$2:H$100=H2,IF(Sheet2!I$2:I$100=I2,1)),0))

Which is an array formula and therefore needs entering with CTRL+SHIFT+ENTER, before copying down.

I have not accounted for all the Columns of relevance, but I'm sure you get the idea and you just need to continue the logic.

Note that this is the same solution Aladin posted earlier. Given the number of Columns involved though, it will take time to calculate. Better to use concatenation, as has already been recommended.

Matty

 

[Aladin Akyurek]

Like I said I have it no more and I did do ctrl+sh+ent. Shall we start again with the exact columns and a couple of extra ones!

The result needs to start in C2 on sheet 1 and needs to look at columns H, I, M, O, P, Q, R and T. If all the data matches in those columns on both sheets then I need what is in column C on sheet 2 put in column C on sheet 1.

The sheets are approx 40 columns wide by 31,000 rows.

Care to specify what you have in H, I, M, O, P, Q, R and T? Text, etc.

 

[Dazzawm]

Care to specify what you have in H, I, M, O, P, Q, R and T? Text, etc.

H=text
I=text & numbers
M=numbers
O, P, Q, R=numbers
T=text

 

[Aladin Akyurek]

H=text
I=text & numbers
M=numbers
O, P, Q, R=numbers
T=text

Which cells on Sheet1 do house look up values corresponding to the foregoing columns?

 

[Dazzawm]

Which cells on Sheet1 do house look up values corresponding to the foregoing columns?

Sorry don't understand

 

[Aladin Akyurek]

Sorry don't understand

Which cell from Sheet1 match H, which cell match I, etc?

 

[Dazzawm]

It will be exactly the same columns in sheet 1 that looks at the columns in sheet 2

 

[Aladin Akyurek]

It will be exactly the same columns in sheet 1 that looks at the columns in sheet 2

Let's try out the concatenation approach...


In U2 enter and copy down:

=H2&"|"&I2&"|"&M2&"|"&O2&"|"&P2&"|"&Q2&"|"&R2


In C2 on Sheet1 just enter and copy down:


=INDEX(Sheet2!$C$2:$C$30001,MATCH(H2&"|"&I2&"|"&M2&"|"&O2&"|"&P2&"|"&Q2&"|"&R2,Sheet2!$U$2:$U$30001,0))