VLOOKUP excel problem

[mizacasa]

I have an Excel homework that my teacher gave.
basically, i need to so call create a "search" feature such that if a user enter a "rank" number, my formula will provide the name and salary of the employee. i was thinking of using vlookup, but it didn't work. I think the problem may be due to the "rank" column is not in the first column and i'm using a rank function for that column.
does anyone have any idea how to do this?
thanks. i'm using excel 2010 btw.

[jeffreybrown]

Maybe look into Index/Match

http://blog.contextures.com/archives...dex-and-match/

[mizacasa]

I tried using that. But i'm not good with that function. Do you have any idea how to create it?

[vlady]

Hello, good morning,

or Look at this thread for the sample files

used vlookup in this one:
=VLOOKUP(--LEFT(J2,4),A1:B100,2,FALSE)

http://www.excelforum.com/showthread...t=#post2686800

Cheers,
vlady

[jeffreybrown]

Here is an example...

I want to return what is in column A by matching the number 4 in column E.

=INDEX($A$1:$A$16,MATCH(4,$E$1:$E$16,0))

As you have found out, the Vlookup function is limited in the fact it can only look in one direction, to the right. The Index/Match combination can look both left and right.

[mizacasa]

HEY PEOPLE! THANKS! I'VE DONE IT! I used the INDEX/MATCH function.
Thanks for all the replies! I am ultimatly delightes right now!

[jeffreybrown]

You're very welcome...glad we could help...

...but believe me, it only gets better from here

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save