What's in the adjacent cell?

[Jeff Jensen]

A1 thru L1 has: Red, Blue, Yellow, White, Black, Gold, Brown, Grey, Purple,
Green, Pink, Orange

I want to be able to type "White" (or any color) in F4 and have F5 display
the value of the next column in the same row where white is, in this case
"Black".

What formula would I need to put in F5?

Thank you,
Jeff

 

[Lars-Åke Aspelin]

A1 thru L1 has: Red, Blue, Yellow, White, Black, Gold, Brown, Grey, Purple,
Green, Pink, Orange

I want to be able to type "White" (or any color) in F4 and have F5 display
the value of the next column in the same row where white is, in this case
"Black".

What formula would I need to put in F5?

Thank you,
Jeff

Try this formula in cell F5:

=INDEX(A1:M1,MATCH(F4,A1:L1,0)+1)

Hope this helps / Lars-Åke

 

[מיכ×ל (מיקי) ×בידן]

Try:
=INDEX(A1:L1,MATCH(F5,A1:L1,)+1)
OR:
=OFFSET(A1,,MATCH(F5,A1:L1,))
Micky

 

[Jeff Jensen]

Thanks Micky,

They both work great! I like the INDEX version because it's easier for me to
see how it works and modify. I can't for the life of me understand how you or
anyone else knows these things. I search help forever trying to learn on my
own before giving up and posting in this forum. I don't know what I would do
without this forum!

Thanks again,
Jeff

 

[Lars-Åke Aspelin]

Jeff,
You did not state what result you wanted if you put a value in F4
corresponding to the rightmost value, "Orange" in you example.
The INDEX function gives #REF error if you use A1:L1 as the array.
If you use A1:M1, as I did in my proposal, you get the value from M1
which is maybe not what you want either.
The OFFSET function given by Micky also (if you just replace F5 with
F4) returns the value of M1 in that case.

Hope this helps / Lars-Åke

 

[Jeff Jensen]

Thanks Lars,

Good point: A1:M1 instead of A1:L1 prevents the #REF! error if I type
Orange. Also, I like the match type ,0 rather than , (even though they both
work), it's helpful for a greenhorn like me because it's easier to see and
understand what it's for.

Thanks again,
Jeff