return value for duplicate items (row)

[jocer]

Hi Excel Guru

I have 2 worksheet name and location and i'm using index but it doesn't work.
i have duplicate number but in the location sheet they have different value can someone help me to figure it out what formula will i use.

thanks

[zbor]

Here:

Please Login or Register  to view this content.

---------- Post added at 12:09 PM ---------- Previous post was at 12:08 PM ----------

Here:

Please Login or Register  to view this content.

[jocer]

Thanks zbor but i forgot to tell i need the value in C column of location worksheet to be return in name worksheet. Im lost thanks a lot

[zbor]

Here:

Please Login or Register  to view this content.

[jocer]

HI Zbor

in location worksheet in row 13&14 they have different values and row 22 and 23. It gives me headache to figure out how can i return their value.

Hope you can help me to solve
appreciate your help zbor

[zbor]

Ok, Now I get it.. Here is a solution.

I hope you don't mind that values are in reverse order.

(Also, I did this by A column.. You can do the same by B).

Please Login or Register  to view this content.

[zbor]

Also, you might want to add blank if no match found (now it will return some value):

Please Login or Register  to view this content.

[jocer]

HI Zbor

There's a glitch when i putting in 2000 (instead of 1000) it shows N/A. I have no idea as far as i know i can index more than 1000

Appreciate your help thanks

[jocer]

Please Login or Register  to view this content.

If i change the range up to 2000 the value return to #N/A.

Thanks

[jocer]

thanks zbor it working now i need to put 2000 in the other one

case solved