VLOOKUP too long

Silvermini63

Active Member
Joined
Sep 25, 2006
Messages
293
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top