If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup Cell Address
I'm trying to lookup a value in a list, but return the cell address (or row
number) of where the value was found instead of the value itself. So if my table starts in A1 and looks like this... 3 4 5 6 7 When look up the value of 3 I want to return A1 (or 1). I would use a macro, but that's not an option in this particular case. |
#2
|
|||
|
|||
Lookup Cell Address
On Thu, 11 Feb 2010 17:47:02 -0800, hmmm
wrote: I'm trying to lookup a value in a list, but return the cell address (or row number) of where the value was found instead of the value itself. So if my table starts in A1 and looks like this... 3 4 5 6 7 When look up the value of 3 I want to return A1 (or 1). I would use a macro, but that's not an option in this particular case. Use the MATCH worksheet function. If you just want to return a 1, then: D1: 3 MATCH(D1,A1:A5) If you want to return A1, then add the ADDRESS function: =ADDRESS(MATCH(D1,A1:A5),1,4) --ron |
#3
|
|||
|
|||
Lookup Cell Address
Take a look MATCH function in help menu
"hmmm" wrote: I'm trying to lookup a value in a list, but return the cell address (or row number) of where the value was found instead of the value itself. So if my table starts in A1 and looks like this... 3 4 5 6 7 When look up the value of 3 I want to return A1 (or 1). I would use a macro, but that's not an option in this particular case. |
#4
|
|||
|
|||
Lookup Cell Address
Just a note about using MATCH as has been suggested...
MATCH returns the *relative* position of the lookup_value within the lookup_array. The actual cell address is irrelevant as to how MATCH works and the value it returns. So, do you want the *relative position* or the *actual row number* as a result? Lookup_value = 3 A1 = 3 A2 = 5 A3 = 7 =MATCH(3,A1:A3,0) = 1 A20 = 3 A21 = 5 A22 = 7 =MATCH(3,A20:A22,0) = 1 In this second example, do you want a result of 1 or 20? -- Biff Microsoft Excel MVP "hmmm" wrote in message news I'm trying to lookup a value in a list, but return the cell address (or row number) of where the value was found instead of the value itself. So if my table starts in A1 and looks like this... 3 4 5 6 7 When look up the value of 3 I want to return A1 (or 1). I would use a macro, but that's not an option in this particular case. |
#5
|
|||
|
|||
Lookup Cell Address
On Thu, 11 Feb 2010 21:19:09 -0500, Ron Rosenfeld
wrote: On Thu, 11 Feb 2010 17:47:02 -0800, hmmm wrote: I'm trying to lookup a value in a list, but return the cell address (or row number) of where the value was found instead of the value itself. So if my table starts in A1 and looks like this... 3 4 5 6 7 When look up the value of 3 I want to return A1 (or 1). I would use a macro, but that's not an option in this particular case. Use the MATCH worksheet function. If you just want to return a 1, then: D1: 3 MATCH(D1,A1:A5) If you want to return A1, then add the ADDRESS function: =ADDRESS(MATCH(D1,A1:A5),1,4) --ron An oversight: The MATCH part of the above formulas should be: =match(d1,a1:a5,0) in order to match exactly. --ron |
Thread Tools | |
Display Modes | |
|
|