LOOKUP hassles

[Jason]

I am aware that the lookup value in the VLOOKUP function has to be the first column in the array (according to Excel help)and hence will only return values to the right of the lookup value. What I want to do is lookup a value and return values in cells immediately to the left of the lookup value. Unfortunately it is not a simple matter of just swapping the order of the columns. Thoughts?

 

[tactps]

You need top look at the INDEX and MATCH functions, or OFFSET function.

Provide some more detail and I'm sure someone will help.

 

[Jason]

Here's some more detail (wish I knew how to display the actual spreadsheet...anyway).
In columns A and B (hypothetically) there would be codes such as
A B
abcd milldr
efgh milldr
ijkl harmjo
mnop mckeja

I want to look up all references to mckeja and return the value mnop.

 

[tactps]

Try this;
=INDEX(A:A,MATCH("mckeja",B:B),0)

 

[Jason]

Looks like it should do the job. I'm just trying to decipher it so I can modify it as necessary for different arrays and lookup values. Basically, what the heck does this formula do in simple english.

 

[tactps]

MATCH("mckeja",B:B)
will determine the row number

=INDEX(A:A,MATCH("mckeja",B:B),0)
starts at A1 and looks down 4 rows and accross 0 columns.