IF formula with VLOOKUP help needed

[misspoppy]

Hello,

I'm trying to create a formula that would look at a column (G in my worksheet) and if the word "re-promo" is showing it would look for that word in my lookup data. The cells in this column only show this word or nothing but both are the result of a formula in the column.

For those rows where column G shows blank result I want my formula to look up the word from Column F (also the result of a formula)

Basically if there is text in Column G use that as a precedent to look up data.


My formula is

=IF(G3="re-promo",VLOOKUP(G3,CALS!$A$12:$H$22,IF(H3="SD",3,IF(H3="HD",4))),VLOOKUP(F3,CALS!$A$12:$H$25,IF(I3="LVR.1",IF(H3="SD",3,IF(H3="HD",4)),IF(I3="LVR.2",IF(H3="SD",5,IF(H3="HD",6))))))

Only the first part of the formula works. The moment I delete the cells in Column G the value calculated is incorrect albeit it appears in my lookup data.



Any help is greatly appreciated.

Misspoppy

 

[misspoppy]

I tried it on one of the row with Library features with SD and the value I get is 2.08 instead of 1.20
When I enter re-promo in Column G the MFPB amount changes correctly to 1.92 and if I change the DEF column to HD the amount again shows correct 2.40

For some reason as soon as re-promo is not appearing in column G the formula is not looking for the text appearing in Column F.

 

[gaz_chops]

OK try this one, it does away with col G as well

=IF(AB2="0.600000",VLOOKUP("re-promo",CALS!$A$12:$H$25,IF(H2="SD",3,IF(H2="HD",4))),VLOOKUP(F2,CALS!$A$12:$H$22,IF(I2="LVR.1",IF(H2="SD",3,IF(H2="HD",4)),IF(I2="LVR.2",IF(H2="SD",5,IF(H2="HD",6)))),0))

 

[gaz_chops]

Noticed you're using col G elsewhere!

 

[misspoppy]

It WORKS!!! Thank you so much!!!! I really appreciate your help and soooooooo relieved

 

[gaz_chops]

You're welcome, note that col G is used elsewhere!