VLOOKUP too long

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Jun 4, 2008

• #2

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

Guanjin Peter

Active Member

Joined

May 21, 2008

Messages

429

• Jun 4, 2008

• #3

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

Guanjin Peter

Active Member

Joined

May 21, 2008

Messages

429

• Jun 4, 2008

• #4

anyone?

 

Upvote 0

S

Silvermini63

Active Member

Joined

Sep 25, 2006

Messages

293

• Jun 5, 2008

• #5

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

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jun 5, 2008

• #6

Silvermini63 said:

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

Click to expand...

Is the outcome a number or text?

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jun 5, 2008

• #7

Guanjin said:

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?

Click to expand...

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

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

43,344

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Jun 5, 2008

• #8

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

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jun 5, 2008

• #9

Silvermini63 said:

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

Click to expand...

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