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?

 

[Fluff]

That doesn't really tell me anything. Can you post some sample data & explain exactly what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[EVCOdan]

That doesn't really tell me anything. Can you post some sample data & explain exactly what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Due to IT contraints I cant install the add-in, however I can link the data from the cells.

Item Browse spreadsheet

Ord Qty

Setup Time

Run Time

Yield

Routing Code

Site

Prod Line

Memo T

Serial Control

Customer

Mold

Status

Buyer/Planner

Design Group

Promotion Group

Drawing

Drawing Loc

Drawing Size

Added

DEV BEGIN

DEV END

User ID

DEV QTY

Manufacturing Lead Time

Cumulative Lead Time

Part Weight (QAD)

Cycle Time

Redirect

1536

0.0026042

0.0061111

100

4

1400

Never

RW

5170

ACT

os

77103-132

44600

0

0

rwh

0

10

104

#N/A​

9400137​

Routing Data spreadsheet

Concatenate

Routing Code

Operation

Start Date

End Date

Standard Operation

Work Center

Machine

Yield Percent

Description

Machines per Operation

Milestone Operation

Setup Crew

Run Crew

Setup Time

Run Time

Supplier

Tool Code

Move Next Operation

Auto Labor Report

Modified Date

9400136L

9400136

20

300

LABOR

L

100.00%

SETUP & RUN 300-499 TONS

1

No

1.00

1.00

1.5

0.010555555

Yes

No

11/23/2021

9400136M

9400136

30

300

300

M

97.00%

SETUP & RUN 300-499 TONS

1

No

1.00

1.00

1.5

0.010555555

Yes

No

11/23/2021

9400138L

9400138

25

4/28/2020

BA

L

100.00%

BASIC ASSEMBLY & SETUP

1

No

1.00

1.00

0.5

0.011111111

Yes

No

4/15/2022

9400138M

9400138

30

4/28/2020

BA

M

100.00%

BASIC ASSEMBLY & SETUP

1

Yes

1.00

1.00

0.5

0.011111111

Yes

No

4/15/2022

As you can see above 9400137 does not exist on the Routing Data sheet. Therefore I will not be able to pull data from this location.

I've rethought the procedure and based on the data, it would be best to run the formula the way you did original, but if the formula does not find anything on the Routing Data sheet because the item does not exist, then search in Column M for the data on the primary sheet. The reason i say this is because the data in column M on the primary sheet is total data and only applicable if the item is complete without and L event which is seperated by lines on the Routing Data sheet.

So in short first run the below formula (This formula is placed in cell AK on the Item Browse worksheet);

=LET(v,VLOOKUP(O2 & "M",'Routing Data'!A:P,16,FALSE),IF(IFNA(v,"")="",VLOOKUP(A2 & "M",'Routing Data'!A:P,16,FALSE),v))*3600

If this formula does not find anything, then used value from column M of item browse.


Hope this helps make a better understanding.

 

[Fluff]

Maybe

=LET(v,VLOOKUP(O2 & "M",'Routing Data'!A:P,16,FALSE),IF(IFNA(v,"")="",ifna(VLOOKUP(A2 & "M",'Routing Data'!A:P,16,FALSE),M2),v))*3600

 

[EVCOdan]

Maybe

=LET(v,VLOOKUP(O2 & "M",'Routing Data'!A:P,16,FALSE),IF(IFNA(v,"")="",ifna(VLOOKUP(A2 & "M",'Routing Data'!A:P,16,FALSE),M2),v))*3600

Works like a charm! Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.