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]

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.

Control+shift+enter, not just enter:

=INDEX(Sheet2!$C$2:$C$400,MATCH(1,IF(Sheet2!$B$2:$B$400=B2,
    IF($D$2:$D$400=D2,IF($E$2:$E$400=E2,IF($F$2:$F$400,1)))),0))

 

[Dazzawm]

Control+shift+enter, not just enter:

=INDEX(Sheet2!$C$2:$C$400,MATCH(1,IF(Sheet2!$B$2:$B$400=B2,
    IF($D$2:$D$400=D2,IF($E$2:$E$400=E2,IF($F$2:$F$400,1)))),0))

So I enter this is C1 on sheet 1 ? Also it has to look at about 30000 rows does that matter?

 

[Aladin Akyurek]

So I enter this is C1 on sheet 1 ?

I took the data as starting at row 2, hence C2 as the formula cell. If the start is at row 1, C1 would be OK.

Also it has to look at about 30000 rows does that matter?

It depends on the number of look ups that you want to carry out. If that number is considerable, we can use concatenation.

In G2 on Sheet2 enter and copy down:

=B2&"|"&D2&"|"&E2&"|"&F2

still assuming row 2 as start row (on both Sheet1 and Sheet2).

Now we can invoke on Sheet1...

=INDEX(Sheet2!$C$2:$C$30000,MATCH(B2&"|"&D2&"|"&E2&"|"&F2,Sheet2!$G$2:$G$30000,0))

 

[mikerickson]

perhaps
=INDEX(Sheet2!$C$1:$C$100, SUMPRODUCT(MATCH(B1&D1&E1&F1,Sheet2!$B$1:$B$3000&Sheet2!$D$1:$D$3000&Sheet2!$E$1:$E$3000&Sheet2!$F$1:$F$3000,0)), 1)

 

[Dazzawm]

Tried all suggestions and only get #N/A all the time. Is then any code I can run that will do the trick? I must admit I didnt give you the exact column letters correctly but I changed the letters accordingly.

 

[Aladin Akyurek]

Tried all suggestions and only get #N/A all the time. Is then any code I can run that will do the trick? I must admit I didnt give you the exact column letters correctly but I changed the letters accordingly.

What did you try exactly?

 

[Dazzawm]

All of yours and Mikes but N/As when I know there are matches. Is there a macro that would do the job rather than a formula?

 

[Aladin Akyurek]

All of yours and Mikes but N/As when I know there are matches. Is there a macro that would do the job rather than a formula?

I meant: What is the exact the formula you tried out in your spreadsheet?

 

[Dazzawm]

I meant: What is the exact the formula you tried out in your spreadsheet?

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