SOLVED

IF and VLOOKUP working incorrectly.

Iron Contributor

Hi, 

I have created a Pivot Table and then I use VLOOKUP and IF to get Lookup_value from that table.

=IFERROR(IF(NUMBERVALUE(TEXT(TODAY()-7,"[$-en-US]d-mmm;@"))=$D$1,VLOOKUP($A2,Pivot!$D:$I,2,FALSE),""),0)

 

with : $D$1=10-Oct.
The expected result is not correct, because it contains some values of other Dates.

Hope for your helps.

Thank you.

4 Replies
best response confirmed by littlevillage (Iron Contributor)
Solution

@littlevillage 

Your VLOOKUP only looks at column D, not at column B of the Pivot sheet. Try

 

=IFERROR(INDEX(Pivot!E:E,MATCH(1,(Pivot!B:B=$D$1)*(Pivot!D:D=A2),0)),"")

 

@Hans Vogelaar 

Thank you for your help.

The default value of first argument in your Match function is 1. Can you explain more.?

@littlevillage 

=IFERROR(INDEX(Pivot!E:E,MATCH(1,(Pivot!B:B=$D$1)*(Pivot!D:D=A2),0)),"")

 

(Pivot!B:B=$D$1) returns a series of TRUE/FALSE values.

(Pivot!D:D=A2) also returns a series of TRUE/FALSE values.

In calculations, Excel treats TRUE as 1 and FALSE as 0, so the result of (Pivot!B:B=$D$1)*(Pivot!D:D=A2) is a series of 1s and 0s: 1 where both conditions are met, 0 otherwise.

MATCH(1,(Pivot!B:B=$D$1)*(Pivot!D:D=A2),0) searches for the first 1 and returns its position (or an error).

@Hans Vogelaar 

I actually got it, It's really beyond my imagination Excel is Excellent.
Thank you again for your helps.

1 best response

Accepted Solutions
best response confirmed by littlevillage (Iron Contributor)
Solution

@littlevillage 

Your VLOOKUP only looks at column D, not at column B of the Pivot sheet. Try

 

=IFERROR(INDEX(Pivot!E:E,MATCH(1,(Pivot!B:B=$D$1)*(Pivot!D:D=A2),0)),"")

 

View solution in original post