Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What function would tell me the cell the information is in?
Hi,
As an example to a larger project I'm working, I have two columns of data I want to search for an entry, "dogs" for example. And I'd like a function that returns the cell location (or row, column is fine too) in which "dogs" has been found. I know lookup, match, offset, not very well, and I'm having trouble when the search area is more then one row AND one column in dimensions. As a simpler example, set up a spreadsheet like this : Column A Column B Column C Row 1 1 6 8 2 2 7 B3 3 3 8 4 4 9 5 5 10 The numbers 1 - 10 are entered in two columns between rows 1 and 5. I enter the number I want to locate into cell C1 (I enter "8"). The function in cell C2 returns the call reference "B3" meaning that the value "8" was found at location B3. If I entered "4", then the result would be "A4". What function do I use? Thanks. - Eric Anchorage, Alaska |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What function would tell me the cell the information is in?
Based on your posted sample, try this array formula** :
C1 = 8 =ADDRESS(MAX((A1:B5=C1)*ROW(A1:B5)),MAX((A1:B5=C1) *COLUMN(A1:B5)),4) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "ak_edm" wrote in message ... Hi, As an example to a larger project I'm working, I have two columns of data I want to search for an entry, "dogs" for example. And I'd like a function that returns the cell location (or row, column is fine too) in which "dogs" has been found. I know lookup, match, offset, not very well, and I'm having trouble when the search area is more then one row AND one column in dimensions. As a simpler example, set up a spreadsheet like this : Column A Column B Column C Row 1 1 6 8 2 2 7 B3 3 3 8 4 4 9 5 5 10 The numbers 1 - 10 are entered in two columns between rows 1 and 5. I enter the number I want to locate into cell C1 (I enter "8"). The function in cell C2 returns the call reference "B3" meaning that the value "8" was found at location B3. If I entered "4", then the result would be "A4". What function do I use? Thanks. - Eric Anchorage, Alaska |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet function for aggregating information | Excel Worksheet Functions | |||
how do i write a function to display a list of information | Excel Worksheet Functions | |||
a function that counts the amount of cells with information in them | Excel Discussion (Misc queries) | |||
Function to find expired information | New Users to Excel | |||
information function - does cell contain reference | Excel Worksheet Functions |