Difficult V Look up and if function together problem

[tentop6521]

Okay, so I am trying to use if function with V look to solve this problem, but I can't seem to figure it out. Using the "look up table" I am trying to fill the "PROJ" column with values from the "look up table" sheet. I want to first look up values on the "Proj Orders" column, but if that value is blank, I want to use to values from the "Est. Ord." column.

Attached is a sample file .Excel_Help.xlsx

Please help Excel Masters.

[judgeh59]

will this work

Please Login or Register  to view this content.

[tentop6521]

Doesn't work because when column 4 on the "look up table" is blank, I need to then get the value of the column 2.

[FDibbins]

A slightly different approach, although judge's is shorter...
=MAX(INDEX('Look up table'!D:D,MATCH(Sheet3!A4,'Look up table'!A:A,0)),INDEX('Look up table'!B:B,MATCH(Sheet3!A4,'Look up table'!A:A,0)))

[tentop6521]

Can this be done using combination of vlook up and If function??

[judgeh59]

this seems to work

Please Login or Register  to view this content.

[judgeh59]

My original didn't work because I was using IFERROR and the Error didn't happen....

[judgeh59]

@Fdibbins - the new one is only slighty shorter than yours....I do like INDEX/MATCH.

[tentop6521]

Excel_Help.xlsx

okay now I am able to get the look up value on column 2, but the first v lookup on column 4 comes up as a Name error. Not sure what's going on. I've attached it here.

[judgeh59]

the Second VLOOKUP got messed up in the copy/paste.....it says Vlook not VLOOKUP

[tentop6521]

I am so stupid . Thanks for being patient with me. Really appreciate the help

[judgeh59]

not a problem....I like the pilot error problems.....easy fix....have a great day....

[judgeh59]

thanks for the rep points