• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup match help

E

ExcelSur

Member
Please help me with Vlookup and Match functions. I have attached a sample file with data.

I used this formula from one of the questions from this forum and I get #N/A


VLOOKUP(G7,$B$8:$D$17,MATCH(G8,C8:D17,0),0)


Thanks
 

Attachments

  • vlookup_match.xlsx
    12.5 KB · Views: 19
The lookup_value (2) is greater than all the values in the lookup_vector (which it will be, because of the 1/ before the lookup_vector).

We use a lookup_value of 2 because we know that the result of this expression:
1/((B8:B17=G7)*(C8:C17=G8))
will not return a value greater than 1. Therefore, the lookup_value 2 is guaranteed to be greater than all the 1s in the lookup_vector.

The position of the 1 result in the lookup_vector matches the position of the item we are looking for in the result_vector.

Use the Evaluate Formula feature on the Formulas ribbon to step through the formula and see it working.
 
ExcelSur said:
Please help me with Vlookup and Match functions. I have attached a sample file with data.

I used this formula from one of the questions from this forum and I get #N/A


VLOOKUP(G7,$B$8:$D$17,MATCH(G8,C8:D17,0),0)


Thanks
Click to expand...


Another Option using sumproduct

=SUMPRODUCT(($B$8:$B$17 = G7) * ($C$8:$C$17 = G8), D8:D17)
 
AliGW said:
The lookup_value (2) is greater than all the values in the lookup_vector (which it will be, because of the 1/ before the lookup_vector).

We use a lookup_value of 2 because we know that the result of this expression:
1/((B8:B17=G7)*(C8:C17=G8))
will not return a value greater than 1. Therefore, the lookup_value 2 is guaranteed to be greater than all the 1s in the lookup_vector.

The position of the 1 result in the lookup_vector matches the position of the item we are looking for in the result_vector.

Use the Evaluate Formula feature on the Formulas ribbon to step through the formula and see it working.
Click to expand...

AliGW,
This is hard to understand. Looks like I got alot to learn. Thanks for your help
 
sureshsonti said:
Another Option using sumproduct

=SUMPRODUCT(($B$8:$B$17 = G7) * ($C$8:$C$17 = G8), D8:D17)
Click to expand...
AliGW said:
The lookup_value (2) is greater than all the values in the lookup_vector (which it will be, because of the 1/ before the lookup_vector).

We use a lookup_value of 2 because we know that the result of this expression:
1/((B8:B17=G7)*(C8:C17=G8))
will not return a value greater than 1. Therefore, the lookup_value 2 is guaranteed to be greater than all the 1s in the lookup_vector.

The position of the 1 result in the lookup_vector matches the position of the item we are looking for in the result_vector.

Use the Evaluate Formula feature on the Formulas ribbon to step through the formula and see it working.
Click to expand...


AliGW,
Is this (D8:D17) the result vector and is this (B8:B17=G7)*(C8:C17=G8) the lookup vector.

Thanks
 
a couple more alternatives
to confirm with CSE
=INDEX(D8:D17,MATCH(G7&G8,B8:B17&C8:C17,0))
or without
=INDEX(D8:D17,AGGREGATE(14,6,ROW(D8:D17)/((B8:B17=G7)*(C8:C17=G8)),1)-7,0)
 

Attachments

  • Copy of vlookup_match.xlsx
    12.6 KB · Views: 4
1] If you want to use VLOOKUP (), try this array formula :

=VLOOKUP(G7,IF(C8:C17=G8,B8:D17),3,0)

p.s. array formula : to be confirmed enter with CTRL+SHIFT+ENTER (CSE) instead of just ENTER

2] Or this without CSE :

=SUMIFS(D:D,B:B,G7,C:C,G8)

Regards
Bosco
 
bosco_yip said:
1] If you want to use VLOOKUP (), try this array formula :

=VLOOKUP(G7,IF(C8:C17=G8,B8:D17),3,0)

p.s. array formula : to be confirmed enter with CTRL+SHIFT+ENTER (CSE) instead of just ENTER

2] Or this without CSE :

=SUMIFS(D:D,B:B,G7,C:C,G8)

Regards
Bosco
Click to expand...


Thanks Bosco
 
You must log in or register to reply here.
Back
Top