Forum Discussion

Iron Contributor

Oct 17, 2022

Solved

IF and VLOOKUP working incorrectly.

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.

IF_VLOOKUP.xlsx2.4 MB

Formulas and Functions

HansVogelaar
Oct 17, 2022
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)),"")

4 Replies

HansVogelaar
MVP
Oct 17, 2022
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)),"")
- littlevillage
  Iron Contributor
  Oct 18, 2022
  HansVogelaar
  Thank you for your help.
  The default value of first argument in your Match function is 1. Can you explain more.?
  - HansVogelaar
    MVP
    Oct 18, 2022
    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).

Resources