Vlookup within an If statement

[m15tyw00d]

I have the following If Statement
=IF(ISNUMBER(B11),VLOOKUP(B11,Products!B3:M314,11,FALSE),"")

What I need to do:
if there is a # in B11 then do the Vlookup to find the product, then I want it to look in column 5 of that found product row to see if there is a #. If so, then return column 4 of that row. if not, return column 8 of that row.

How do I do that?

Misty

[alansidman]

I did this with a helper column. Your original formula should read as: =IF(ISNUMBER(B11)=TRUE,VLOOKUP(B11,Products!B3:M314,11,FALSE),"")

Assume your formula is in cell D1, then in the helper column =IF(ISNUMBER(D1)=TRUE,VLOOKUP(B11,Products!B3:M5,4,FALSE),VLOOKUP(B11,Products!B3:M5,8,FALSE))

[jason.b75]

Maybe something like

=IF(ISNUMBER(B11),IF(ISNUMBER(--(VLOOKUP(B11,Products!$B$3:$F$314,5,0)&"")),VLOOKUP(B11,Products!$B$3:$E$314,4,0),VLOOKUP(B11,Products!$B$3:$I$314,8,0)),"")

[jason.b75]

I did this with a helper column. Your original formula should read as: =IF(ISNUMBER(B11)=TRUE,VLOOKUP(B11,Products!B3:M314,11,FALSE),"")

That would incorrectly suggest that there is a problem with the original formula.

ISNUMBER() already returns a Boolean value, so to test that again as you have done, i.e. IF(TRUE=TRUE, is a superfluous and pointless action.

[alansidman]

Jason is correct. Thanks for noting that. Overthinking sometimes causes this type of senior moment.

[m15tyw00d]

that didn't work. let me try and post a pic...first worksheet.jpgsecond worksheet.jpg
on the order sheet
E11 is where I put your formula. it returned a 0
In that cell I need the following returned:
I need it to look in B11 to see the product code
It needs to search that code on the Products worksheet (range above)
when It finds the row (it happens to be row 134), I need it to look in column F (the 5th column)
if there is a number in that column then it needs to return column 4 (which in this example should be GL)
if there is not a number in that column then it needs to return column 10. (sorry miscounted earlier...)

Jason.B when I put your code in
=IF(ISNUMBER(B11),IF(ISNUMBER(--(VLOOKUP(B11,Products!$B$3:$F$314,5,0)&"")),VLOOKUP(B11,Products!$B$3:$E$314,4,0),VLOOKUP(B11,Products!$B$3:$I$314,8,0)),"")
I get #REF

[jason.b75]

Overthinking sometimes causes this type of senior moment.

Some of us don't need to overthink to have senior moments, they just occur frequently without warning

mistywood, could you attach a sample spreadsheet to your post instead of screen captures, that way we don't have to spend hours retyping your data to test before we suggest.

[jason.b75]

How do you relate the data in your first screen cap to row 134 of the second screen cap?

I can't see anything that matches those 2 records.

Formula shouldn't return #REF unless you made any changes to it, as I said in my previous post, it's easier and more accurate for us to work with a real spreadsheet than a screen cap.

[m15tyw00d]

how do I attach a file

[jason.b75]

Click on GO ADVANCED and use the paperclip icon to open the upload window. The picture linked below shows where to find it.

View Pic

[m15tyw00d]

ok here is the attachment. hope it helps.
Order Sheet example.xlsm

[jason.b75]

The formula broke because you change 8 to 10 but didn't expand the range, so it was looking for a column that didn't exist.

See if this gives the expected results.

=IF(ISNUMBER(B11),IF(ISNUMBER(--(VLOOKUP(B11,Products!$B$3:$F$4,5,0)&"")),VLOOKUP(B11,Products!$B$3:$E$4,4,0),VLOOKUP(B11,Products!$B$3:$K$4,10,0)),"")

[m15tyw00d]

that works perfect!!! Thank you!!

[jason.b75]

An alternative method that also works with your sample data.

Just to point out that there is nothing wrong with the last one, it just looks wrong to me because it is long and repetitive. This one is more concise, although probably harder to understand.

=IFERROR(LEFT(LOOKUP("y",VLOOKUP(B11,Products!$B$3:$K$4,{10,4},0)&"z"),2),"")