LOOKUP Worksheet function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an empty cell (C5)that I'm placing text in. I have
two columns of text C116:C123 & B116:B123). I want the
text I'm placing in C5 to be checked against the text in
C116:C123, if there's a match, I want the result of the
corresponding text in B116:B123. Here's the formula I used.
It's not working, any suggestions?
=LOOKUP(C5,C116:C123,B116:B123)
 
This does work but you must ensure that the items in column C are in order
eg 1,2,3,4 or A,B,C,D

If you could make your spreadsheet so that you lookup the values in column B
and return the value of column C then you could use the VLOOKUP function
which will allow you to have lookup items that are not in any particular
order

eg
=VLOOKUP(C5,B116:C123,2,false)

HTH
 
Lookup formulas always look in the first row or column, so
you have two choices.
1). swap round the two columns of data.
2. Use an Index formula instead, in this case:

=index(B116:B123,Match(c5,C116:c123,0),1)

Cheers, Pete
 
<<"Lookup formulas always look in the first row or column">>

That's not really accurate.

Vlookup and Hlookup *always* look in the first row or column for the lookup
value.
Lookup, on the other hand, has two forms, vector and array.
The OP used the vector form, which is evident by the fact that there are two
arrays entered in his formula.
In the vector form, the lookup value is searched for in the array *first*
entered in the formula and returns values from the second array.

There are *no restraints whatever* on the placements of the two arrays.
In fact, one array can be a column and the other can be a row.

For example, this is a valid formula:

=LOOKUP(A1,S81:AB81,C12:C21)

However, the values in the lookup array *must* be placed in ascending order
to insure an accurate return, which is probably the reason the OP is
experiencing a problem.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Lookup formulas always look in the first row or column, so
you have two choices.
1). swap round the two columns of data.
2. Use an Index formula instead, in this case:

=index(B116:B123,Match(c5,C116:c123,0),1)

Cheers, Pete
 
Lookup, on the other hand, has two forms, vector and array.
The OP used the vector form, which is evident by the fact that there are two
arrays entered in his formula.
Click to expand...

I didn't know that. I'm only familiar with the array form
and even then only by extension from VLOOKUP.

I stand corrected.

Pete
 
Back
Top