Forum Discussion

littlevillage's avatar
littlevillage
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,FALS...
excel
Formulas and Functions
  • HansVogelaar's avatar
    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)),"")

     

littlevillage's avatar
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's avatar
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).

  • littlevillage's avatar
    littlevillage
    Iron Contributor
    Oct 18, 2022

    HansVogelaar 

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

Resources