VLOOKUP too long

[Silvermini63]

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))))

 

[schielrn]

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]

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?

 

[Guanjin Peter]

anyone?

 

[Silvermini63]

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

 

[Aladin Akyurek]

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?

 

[Aladin Akyurek]

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]

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))

 

[Aladin Akyurek]

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...

=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))