Vlookup with 2 criteria - should this be done via INDEX MATCH

[M1donne]

Hi all,

I hope all is well and lock-down is easing safely where you are.

It has been a while since I have posted so apologies if my format is a little rusty.

I have two tables and I want to extract a field from the 2nd table based on where two criteria are met (from the first table) - I tried using vLookup which doesn't have any criteria built in and then looked at using INDEX MATCH but I'm struggling to get the formula to work...

=INDEX(TABLE2!$H$2:$H$6232,AND(MATCH(A2,TABLE2!$A$2:$A$6232,0),MATCH(D2,TABLE2!$D$2:$D$6232,0)))

Where A2 and D2 are the Criteria in TABLE1 and the target field resides in TABLE2!$H$2:$H$6232

I don't know if this can be done via INDEX MATCH but if so, can you please point me in the direction of my error?

Much appreciated

MD

 

[navinrb]

Hi all,

I hope all is well and lock-down is easing safely where you are.

It has been a while since I have posted so apologies if my format is a little rusty.

I have two tables and I want to extract a field from the 2nd table based on where two criteria are met (from the first table) - I tried using vLookup which doesn't have any criteria built in and then looked at using INDEX MATCH but I'm struggling to get the formula to work...

=INDEX(TABLE2!$H$2:$H$6232,AND(MATCH(A2,TABLE2!$A$2:$A$6232,0),MATCH(D2,TABLE2!$D$2:$D$6232,0)))

Where A2 and D2 are the Criteria in TABLE1 and the target field resides in TABLE2!$H$2:$H$6232

I don't know if this can be done via INDEX MATCH but if so, can you please point me in the direction of my error?

Much appreciated

MD

Hi there,
use this formula
=vlookup(A2&" "&D2,choose({1,2},TABLE2!$A$2:$A$6232&" "&TABLE2!$D$2:$D$6232,TABLE2!$H$2:$H$6232 ),2,0)
since this is array formula hit CTRL+ SHIFT +ENTER to work.

 

[Fluff]

Another option
=INDEX(TABLE2!$H$2:$H$6232,MATCH(A2&"|"&H2,TABLE2!$A$2:$A$6232&"|"&TABLE2!$D$2:$D$6232,0))

Might need CSE entry.

 

[M1donne]

=INDEX(TABLE2!$H$2:$H$6232,MATCH(A2&"|"&H2,TABLE2!$A$2:$A$6232&"|"&TABLE2!$D$2:$D$6232,0)) didn't work (even as array formula), can I ask what the | is for?

 

[Fluff]

It's just a separator, to keep the cell values distinct.

 

[M1donne]

=vlookup(A2&" "&D2,choose({1,2},TABLE2!$A$2:$A$6232&" "&TABLE2!$D$2:$D$6232,TABLE2!$H$2:$H$6232 ),2,0) no luck either sadly.

 

[Fluff]

Just realised there's a typo on mine, it should be
=INDEX(TABLE2!$H$2:$H$6232,MATCH(A2&"|"&D2,TABLE2!$A$2:$A$6232&"|"&TABLE2!$D$2:$D$6232,0))

 

[navinrb]

=vlookup(A2&" "&D2,choose({1,2},TABLE2!$A$2:$A$6232&" "&TABLE2!$D$2:$D$6232,TABLE2!$H$2:$H$6232 ),2,0) no luck either sadly.

hey,
its space between double quotes and did you use ctrl +shift +enter. i think it should be working .you can display your screenshot of the errors formore clarity of your query.

 

[M1donne]

Thanks all, massively appreciated, in the end I defaulted to my Getpivotdata based on 2 criteria, not as pretty but very effective.

Thanks again.

 

[Fluff]

Glad you sorted it & thanks for the feedback.