Help with formula on Pivot Table

[Noxqss38242]

Currently I have this formula:
=VLOOKUP(P7,data,2,0)/$Q$5

This formula works!
However, now I want to see "ALL" months' data and want it to say if B3 = (All) then =VLOOKUP(P7,data,14,0)/$Q$5
As you can tell the formula needs to work as well if (All) is not selected as I will also be looking at months individually so it has to ignore B3 if (All) is not selected.

Anyone know how to add this into the formula?


Thank you!

 

[etaf]

=IF( B3 = "(ALL)", VLOOKUP(P7,data,14,0)/$Q$5 , VLOOKUP(P7,data,2,0)/$Q$5)
OR you could use in the vlookup itself

=VLOOKUP(P7,data,IF(B3="(ALL)",14,2),0)/$Q$5

 

[Noxqss38242]

Awesome!

Thank you so much!

 

[etaf]

you are welcome
Not sure about the pivot table in your title - but if thats solves the problem , great

 

[Noxqss38242]

Wasn't sure if it being a pivot table would affect anything.

I think I'm stuck on this last data pull, though.

=VLOOKUP($B$3,TracMiles!$F$42:$R$43,2,0) (which I know it is only 2 rows now but as the months progress it will go down to row 54)

This deals with "months in service"
I may just have to have it say, if B3 says ALL then return blank

Not sure for it to say, if B3 = All then pull data from last row

If you know, great, if not, I'll just report blank

 

[Noxqss38242]

=IF($B$3="(All)",LOOKUP(2,1/(NOT(ISBLANK(TracMiles!G42:G43))),TracMiles!G42:G43),VLOOKUP($B$3,TracMiles!$F$42:$R$43,2,0))

This works!

Shout out to ETAF

With your original formula tweak, I was able to figure more out. Thanks again!

 

[etaf]

glad its now working for you