When one cell equals value of another cell, then third cell displays text of fourth cell?

Crazy right!


I'll try to keep this a short and to the point as possible.

If any cell in range A1:A5 = the numeric value of D1, then C1 displays the text of B1:B5, where B1:B5 is the name of the cells A1:A5.

Ex: D1 is set to 11. A3 is set to 11. B3 is set to "Steve". C1 would need to display "Steve".
D1 is set to 22. A5 is set to 22. B5 is set to "Frank". C1 would need to display "Frank".

I already have this formula working but i need to add the ability to set D1 to any number i choose and then have the formula look for that number in A1:A5 and return the name in C1.
=IFERROR(IFERROR(INDEX(A1:A5, MATCH(10, B1:B5, 0)), INDEX(A1:A5, MATCH(20, B1:B5, 0))), "")

OK, that works for my example but i'm having trouble translating it in to my actual project. I guess i should have just used my real cells and values to begin with.

D1 is actually P3. C1 (where the formula is entered) is actually AD1. The range where the numbers are is actually B8:I8 and the names of those cells are right above that in B6:I6.

I'm guessing your formula doesn't work because it's looking in a column where as my names and numbers are in a row. I tried adapting it but i can't get it to work.

Sometimes my making things "as short and to the point as possible" ends up making things harder!

Tony...you are the man!

Hello,

I would like a formula that Returns/Displays the Values of Column(Variable)of SET B in Column (Variable)of SET A
if the NAME in SET A Equals the Name in SET B.

If any cell in range C6:C13 = any cell in C17:C23, then D6:D13 displays the corresponding text of D17:D23.

I hope the description of help I need is clear.


Thanks.