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,FALS...

IF_VLOOKUP.xlsx2.4 MB

excel

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)),"")

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).
- 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.