VLOOKUP too long

Silvermini63

Active Member
I am just wondering if there is a way to simplify the below formula.

All it is is a VLOOKUP that if the data is in B1 the return it if not C1 then return that and if it’s in D1 return that.

=IF(VLOOKUP(A1,Sheet1!A:B,2,FALSE),VLOOKUP(A1,Sheet1!A:B,2,FALSE),IF(VLOOKUP(A1,Sheet1!A:C,3,FALSE),VLOOKUP(A1,Sheet1!A:C,3,FALSE),IF(VLOOKUP(A1,Sheet1!A:D,4,FALSE),VLOOKUP(A1,Sheet1!A:D,4,FALSE))))

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

schielrn

Well-known Member
You could drop the last if statement:

=IF(VLOOKUP(A1,Sheet1!A:B,2,FALSE),VLOOKUP(A1,Sheet1!A:B,2,FALSE),IF(VLOOKUP(A1,Sheet1!A:C,3,FALSE),VLOOKUP(A1,Sheet1!A: C,3,FALSE),VLOOKUP(A1,Sheet1!A:D,4,FALSE)))

Is B1, C1 or D1 always only one of those filled with data or can more than one of those have data?

Hope that helps some.

Guanjin Peter

Active Member
mind if i drop another question?

=VLOOKUP(B4,A:B,1,FALSE)

what can't it, return the column to the left? although B4 there is a match?

anyone?

Silvermini63

Active Member
No only one of the three cells will have data.... so i am only looking for data and ignoring blanks

MrExcel MVP
No only one of the three cells will have data.... so i am only looking for data and ignoring blanks

Is the outcome a number or text?

MrExcel MVP
mind if i drop another question?

=VLOOKUP(B4,A:B,1,FALSE)

what can't it, return the column to the left? although B4 there is a match?

By design...

You need a different route...

=INDEX(A:A,MATCH(E4,B:B,0))

where E4 houses a value to look up.

RoryA

MrExcel MVP, Moderator
Peter,
VLOOKUP always looks up the value in the first column of the table range you pass it. If you need to do a lookup to the left, use a combination of INDEX and MATCH:
=INDEX(A:A,MATCH(B4,B:B,0))

MrExcel MVP
No only one of the three cells will have data.... so i am only looking for data and ignoring blanks

If the outcome is expected to be numeric...

=LOOKUP(9.99999999999999E+307,INDEX(Sheet1!B:D,MATCH(A1,Sheet1!A:A,0),0))

If the coutcome is expected to be text...
Code:
``````=LOOKUP(9.99999999999999E+307,
SEARCH("?*",INDEX(Sheet1!B:D,MATCH(A1,Sheet1!A:A,0),0)),
INDEX(Sheet1!B:D,MATCH(A1,Sheet1!A:A,0),0))``````

Replies
12
Views
269
Replies
7
Views
223
Replies
5
Views
77
Replies
7
Views
230
Replies
1
Views
117

1,191,587
Messages
5,987,508
Members
440,098
Latest member
MickyMouse123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back