can't lookup a value to the left of my variable

[Gluefoot]

I cannot figure out how to return the value of a cell to the left of my
reference. For example I have a table that looks like this....
A B C
1Bob 1 5
2John 2 6
3Tom 3 7
4Tim 4 8

I need to find the number 7 in column C and return the value of the cell in
column A with the same row number. Make sense? i tried something like this:
=LOOKUP(7, C1:C4, A1:A4) but that keeps returning the wrong value. also,
column A is not and cannot be sorted alphabetically.

 

[JBoyer]

Does it have to be set up in that order.. like could you have a table C A B
with your values? The reason I'm asing is because you could use a vlookup but
that only looks at the leftmost column of your table.

 

[Niek Otten]

=INDEX(A1:A4,MATCH(7,C1:C4,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I cannot figure out how to return the value of a cell to the left of my
| reference. For example I have a table that looks like this....
| A B C
| 1Bob 1 5
| 2John 2 6
| 3Tom 3 7
| 4Tim 4 8
|
| I need to find the number 7 in column C and return the value of the cell in
| column A with the same row number. Make sense? i tried something like this:
| =LOOKUP(7, C1:C4, A1:A4) but that keeps returning the wrong value. also,
| column A is not and cannot be sorted alphabetically.

 

[Gluefoot]

unfortunately yes, it has to be in that order.

Does it have to be set up in that order.. like could you have a table C A B
with your values? The reason I'm asing is because you could use a vlookup but
that only looks at the leftmost column of your table.

 

[Dave Peterson]

=index(a1:a4,match(7,c1:c4,0))

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

 

[JBoyer]

The function you asked about should also work though too, I was just
proposing a new idea. I would check the formatting of the cells you are
looking up and the cell you are entering the formula into. you could also try
to do something like this just to see if it works. in a5 enter 7 and in b5
enter your formula =LOOKUP(A5, C1:C4, A1:A4)

 

[Sandy Mann]

Others have given you a formula that works, the reason that your formula
failed is because as it says in Help:

"Important The values in lookup_vector must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value."

Because your sample was ascending it does work with the sample data but may
not with real data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk