If first columns vlookup is blank then vlookup second column

[EVCOdan]

Hi all,

I'm trying to follow the excel formula below.

=IF(VLOOKUP([@CustomerID],Customer,3)=””,
VLOOKUP([@CustomerID],Customer,4),
VLOOKUP([@CustomerID],Customer,3))

Current formula;

=IF(VLOOKUP((O5706 & "M"),'Routing Data'!A:U,16,FALSE)="",VLOOKUP((A5706 & "M"),'Routing Data'!A:U,16,FALSE)*3600,VLOOKUP((O5706 & "M"),'Routing Data'!A:U,16,FALSE)*3600)

The above formula returns an #N/A value if column O is blank.

I'm not sure if this is being effected by the fact that I'm sourcing the data to the "Routing Data" spreadsheet from another excel file.

I've also tried running the below formula;

=IFNA(IF(VLOOKUP((O5704),'Routing Data'!B:U,15,FALSE)="","",VLOOKUP((O5704&"M"),'Routing Data'!A:U,16,FALSE)*3600),VLOOKUP((A5704&"M"),'Routing Data'!A:U,16,FALSE)*3600)

Both scenarios work, but only in one direction.

Any ideas what I'm doing wrong?

 

[SanjayGMusafir]

My suggestion is since you are using Excel 2021 better use XLOOKUP

It includes option to add another lookup if result is not found

 

[Fluff]

How about

=LET(v,VLOOKUP(O5706 & "M",'Routing Data'!A:P,16,FALSE),IF(IFNA(v,"")="",VLOOKUP(A5706 & "M",'Routing Data'!A:P,16,FALSE),v))

 

[EVCOdan]

How about

=LET(v,VLOOKUP(O5706 & "M",'Routing Data'!A:P,16,FALSE),IF(IFNA(v,"")="",VLOOKUP(A5706 & "M",'Routing Data'!A:P,16,FALSE),v))

This worked! Thanks Fluff!

What does "v" represent? Is this just a designation?

 

[Fluff]

It's just a variable & you can rename it to pretty much anything you want.

 

[EVCOdan]

Looks like I found another wrench in the mix. Can I add another column to reference first to that? It appears my column M also has the same data that I need to reference initially from the primary sheet.

 

[Fluff]

How about

=LET(vm,VLOOKUP(M5706 & "M",'Routing Data'!A:P,16,FALSE),vo,VLOOKUP(O5706 & "M",'Routing Data'!A:P,16,FALSE),IF(IFNA(vm,"")<>"",vm,IF(IFNA(vo,"")="",VLOOKUP(A5706 & "M",'Routing Data'!A:P,16,FALSE),vo)))

 

[EVCOdan]

How about

=LET(vm,VLOOKUP(M5706 & "M",'Routing Data'!A:P,16,FALSE),vo,VLOOKUP(O5706 & "M",'Routing Data'!A:P,16,FALSE),IF(IFNA(vm,"")<>"",vm,IF(IFNA(vo,"")="",VLOOKUP(A5706 & "M",'Routing Data'!A:P,16,FALSE),vo)))

Should have explain a little more. I need to check if column M has data other than 0, if it does, use that data otherwise continue to original formula. Column M is supposed to have the same value as column 16 in "routing data" but may or may not have data.

 

[Fluff]

In what way is the formula not working?

 

[EVCOdan]

The formula above is searching through 'Routing Data' sheet for M5706. The data in that location is not valid for that search. The data in that location is my target data which is also located in the Routing Data sheet column 16.