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

 

[gaz_chops]

Can you post sample data either here or to dropbox, be much easier to evaluate what you are doing.

 

[misspoppy]

Working sheet Columns showing are F to L CAT Re-promotion DEF LVR BUYS Share % MFPB Library features HD LVR.2 4 50% 1.6 Library features SD LVR.2 1 50% 1.2 Current A re-promo SD LVR.2 66 60% 2.08 Current A re-promo SD LVR.2 323 60% 2.08 Current E SD LVR.1 94 65% 2.59 Current E SD LVR.1 2856 65% 2.59 Current A re-promo HD LVR.2 25 60% 2.6 Current B re-promo HD LVR.2 24 60% 2.6 Current E SD LVR.1 52 65% 2.59 Current E SD LVR.1 923 65% 2.59 <colgroup><col span="2"><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

The formula I'm working on is in Column L (MFPB)



On a separate tab (CALS) I have the look up values starting from A10

		LVR.1		LVR.2
Category	MLF YEAR 1	MFPB SD	MFPB HD	MFPB SD	MFPB HD
Megahit	44707	2.59	3.11	2.08	2.60
Current A	33000	2.59	3.11	2.08	2.60
Current B	24200	2.59	3.11	2.08	2.60
Current C	10000	2.59	3.11	2.08	2.60
Current D	5000	2.59	3.11	2.08	2.60
Current E	2750	2.59	3.11	2.08	2.60
NTR/DTV/MOW	2000	2.07	2.59	2.08	2.60
NTR/MOW/DTV	2000	2.07	2.59	2.08	2.60
re-promo	0	1.92	2.40	1.92	2.40
Library Features - Megahits	0	1.20	1.60	1.20	1.60
Library features	0	1.20	1.60	1.20	1.60

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

Will these work?

 

[gaz_chops]

No it won't paste into a spreadsheet!
It looks like some of the closing")" for if statements where in wrong place. Try -
=IF(G3="re-promo",VLOOKUP(G3,cals!$A$12:$H$25,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)))))))))))

 

[misspoppy]

I'm trying to share the workbook with you not sure if works

https://onedrive.live.com/redir?resid=A2238C5D77483564!311&authkey=!ALbIhVrC7xqiU0w&ithint=file%2cxlsx

 

[gaz_chops]

Got it, which cell is your formula in?

 

[misspoppy]

Darn. I chopped the size of the data and may have deleted that row. (faceslap)
Can you copy it from my original posting? It should be in the MFPB column

Many thanks

 

[gaz_chops]

Found it in your post above.

 

[misspoppy]

Alternatively because the current formula in MFPB column is working the G column could be get rid of altogether and its formula included in the Column F current formula. But not sure how to do that without messing it up.

 

[gaz_chops]

Test this see if results are correct
=IF(G2="re-promo",VLOOKUP(G2,CALS!$A$12:$H$25,IF(H2="SD",3,IF(H2="HD",4))),VLOOKUP(F2,CALS!$A$12:$H$25,IF(AND(I2="LVR.1",H2="SD"),3,IF(AND(I2="LVR.1",H2="HD"),4,IF(AND(I2="LVR.2",H2="SD"),5,IF(AND(I2="LVR.2",H2="HD"),6))))))