Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's in the adjacent cell?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's in the adjacent cell?
On Thu, 7 Jan 2010 12:56:01 -0800, Jeff Jensen
wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's in the adjacent cell?
Try:
=INDEX(A1:L1,MATCH(F5,A1:L1,)+1) OR: =OFFSET(A1,,MATCH(F5,A1:L1,)) Micky "Jeff Jensen" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's in the adjacent cell?
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 "מיכ×ל (מיקי) ×בידן" wrote: Try: =INDEX(A1:L1,MATCH(F5,A1:L1,)+1) OR: =OFFSET(A1,,MATCH(F5,A1:L1,)) Micky "Jeff Jensen" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's in the adjacent cell?
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 On Thu, 7 Jan 2010 13:41:01 -0800, Jeff Jensen wrote: 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 "????? (????) ?????" wrote: Try: =INDEX(A1:L1,MATCH(F5,A1:L1,)+1) OR: =OFFSET(A1,,MATCH(F5,A1:L1,)) Micky "Jeff Jensen" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's in the adjacent cell?
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 "Lars-Ã…ke Aspelin" wrote: On Thu, 7 Jan 2010 12:56:01 -0800, Jeff Jensen wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force entry into cell, based on validation selection in adjacent cell | Excel Worksheet Functions | |||
How to populate a cell with numeric value based on textselected from pull down in adjacent cell? | Excel Worksheet Functions | |||
Set cell to record date when adjacent cell is filled AND NOT RESET | Excel Worksheet Functions | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
When data match, copy adjacent value to adjacent column | Excel Worksheet Functions |