Look up value below another

[Tardis40]

I would like to look up the text value typed in a cell and from another text list that is not sorted I'd like the result after it. I've looked at Match and Index but can't seem to make them work.

As an example I have a column of unsorted text - Tom, Mary, Bill, Gary in D5 to D8 and in E5 to E8 I have numbers 5, 8, 33, 2222.

In A1 I have "Mary" and would to see what follows "Mary". In case it would be "Bill".

I'd like to display "Bill" in A6 and "33" in B6.

How can I do that?

[SuperTech]

To solve this problem, you need to:

1. Find the position of “Mary” in your list
2. Return the item that comes after it
3. Return the corresponding value from the adjacent column

Here's how to do it:

For Text Value (Display “Bill” in A6)

Code: Select all

=INDEX(D5:D8, MATCH(A1, D5:D8, 0) + 1)

For Number Value (Display “33” in B6)

Code: Select all

=INDEX(E5:E8, MATCH(A1, D5:D8, 0) + 1)

I have attached the test file for your reference.

Test Index match.xlsx

(5.92 KiB) Downloaded 18 times

[Tardis40]

Thank you.