Oct 17 2022 04:18 AM
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.
Oct 17 2022 04:35 AM
SolutionYour 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)),"")
Oct 17 2022 08:44 PM
Thank you for your help.
The default value of first argument in your Match function is 1. Can you explain more.?
Oct 18 2022 01:26 AM
=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).
Oct 18 2022 08:07 AM
I actually got it, It's really beyond my imagination Excel is Excellent.
Thank you again for your helps.
Oct 17 2022 04:35 AM
SolutionYour 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)),"")