excel using 2 lookup columns in vlookup

Question

Basic scenario is I want to use column A2 or B2 to vlookup to a different sheet. A2 and B2 can be different strings, but if either of those match I want to bring in the value from sheet I am looking up to.

The formula below works well if I only care about A2, but the other sheet can have a value that matches either A2 or B2.

Any thoughts on how to modify the formula below so it brings in the lookup value that matches?

=IF('Car Accounts'!A4  ="","",VLOOKUP('Car Accounts'!A4,DCTM!B:B,1,0))

In lamen's terms something like this:

=IF('Car Accounts'!A4  ="","",VLOOKUP('Car Accounts'!**A4 OR B4**,DCTM!B:B,1,0))

Scott Craner · Accepted Answer · 2020-10-13 18:23:53Z

1

use IFFEROR:

=IFERROR(VLOOKUP('Car Accounts'!A4,DCTM!B:B,1,0),VLOOKUP('Car Accounts'!B4,DCTM!B:B,1,0))

answered Oct 13, 2020 at 18:23

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amateurhour35 Over a year ago

Thanks this definitely works just wondering, if I wanted to just make the result be blank if A4 is blank, do you know how to work that in? I Tried putting an if statement around the iferror but it fails: Currently what happens is it runs and displays error when there's not data to lookup with In other words if i put if (expression above, "","") it doesn't do what I would expect it to

Scott Craner Over a year ago

=IF('Car Accounts'!A4 ="","",IFERROR(VLOOKUP('Car Accounts'!A4,DCTM!B:B,1,0),VLOOKUP('Car Accounts'!B4,DCTM!B:B,1,0)))

Collectives™ on Stack Overflow

excel using 2 lookup columns in vlookup

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related