Thread: Conditioned VLOOKUP - look to 1 sheet, if not a certain value look to sheet 2

Hello All,
Used the forums several times for answers, but first post.

I am unable to figure this one out.

I have a multi sheet workbook. I am sorry I can't post due to info contained.

What I am attempting to do is in sheet 1 - I want to place a formula that will look to sheet 2 using a vlookup and a set array. It will look at column 5 in sheet 2 only if the lookup value is present AND the column 5 has a value.

If either the lookup value is NOT present, or even if it is.... that there is NO value in column 5 I want it to look at another "Sheet 3" to pull the value. The value on sheet 3 comes from a system extract and will always be present, but the value (whether lookup or Column 5) is an adjustment form that may or may not be done in any given month.

I seem to have found the formul but can't seem to finish it properly.

=IF(VLOOKUP($A13,'SHEET2'!$A$1:$H$33,5,FALSE),VLOOKUP($A13,'SHEET2'!$A$1:$H$33,5,FALSE),VLOOKUP(A13, SHEET3!A:AA,13,FALSE))

my problems are as follows:

When I leave the adjustment form empty of both the lookup value and the column 5 value I get an "N/A"

When I add the adjustment lookup value without the column 5 value, the formula produces the SHEET 3 value which is what I want it to do

However when I add the column 5 value of ZERO (which is all it would ever be if present) it still produces the SHEET 3 extract result EXCEPT FOR

When the adjustment value in column 5 is greater than ZERO

Essentially put I am trying to take the raw data as the monthly number unless I add an adjustment that is meant to force the number to ZERO (users is the column 13 in Sheet 3 and FORCE ZERO USERS is what the column 5 is)

Can anyone suggest either a better way or help me fix this I have wasted way to much time on this.

Thank you so much.

Hi daddylonglegs,
thanks for trying to help me with this one.

I tried it, but it came up as false sorry.

FYI heading out and wont be able to reply back for a few if not tomorrow.

Morning Daddylonglegs.
Back in and gotta try and figure this one out, I will bee here working on it if you have any other suggestions they are greatly appreciated. I will see if I can do a mock up file without sensitive data to give more to go on.

Regards

TCN

Morning,
Thanks much for coming back to help.

See attached example document Please

the highlighted yellow is the spots in question.
I named the sheets to match the actual doc, but the sheets are in the same order as discussed previously.

On the adjust sheet, I listed 2 companys from the extract
one I put in a forced value and the other I did not.

On Group Monthly which is the output sheet, L1 should show 7 (which comes from extract) until you enter in a Zero on the adjust sheet at E3.

Adjust E4 is at ZERO which is what it SHOULD read on Monthly L2. Unfortunately it reads as 1 (which is the correct number from the extract) except it should be forcing a zero. It seems to be a NULL issue cause if you notice, Change the ADJUST E4 to a number higher than 1 it does update it just wont for the zero.


Thanks again.

You sir are the man.
That worked great and seemed to work in the multiple scenarios as well.

If I could these are in the same form and I need to remove a false result can you help since it is same issue essentially:

=IF(G3=Pricing!$B$18,Pricing!$H$18,IF(G3=Pricing!$B$19,Pricing!$H$19,IF(G3=Pricing!$B$20,Pricing!$H$ 20,IF(G3=Pricing!$B$21,Pricing!$H$21,IF(G3=Pricing!$B$22,Pricing!$H$22,IF(G3=Pricing!$B$23,Pricing!$ H$23))))))

I dont think so. The formula works with two columns

1 selects the type

1 auto inputs the price that corresponds to the type

thanks much daddylonglegs.
It did work.

FYI I sent you a private message if you happen to have a second.

Again, thank you so much for your assistance.

Tim