Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you lookup a value & get the cell reference?
I have a workbook with two sheets. I am trying to look up a value in cell A1
of Sheet 1 in colum A through C of Sheet 2. When it finds the value, I want it to return a reference to the cell where it was found (or somehow tell me where it was found). I could reduce my search to just column A of Sheet 2, but I would prefer not to since there are some cases were the value is in column C. I have not been able to find a lookup function that returns a reference. Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you lookup a value & get the cell reference?
Assuming result is in either column A or C:
=IF(ISNA("A"&MATCH(A1,Sheet2!$A$1:$A$10,0)),IF(ISN A("A"&MATCH(A1,Sheet2!$C$1:$C$10,0)),"","C"&MATCH( A1,Sheet2!$C$1:$C$10,0)),"A"&MATCH(A1,Sheet2!$A$1: $A$10,0)) HTH "S. Bevins" wrote: I have a workbook with two sheets. I am trying to look up a value in cell A1 of Sheet 1 in colum A through C of Sheet 2. When it finds the value, I want it to return a reference to the cell where it was found (or somehow tell me where it was found). I could reduce my search to just column A of Sheet 2, but I would prefer not to since there are some cases were the value is in column C. I have not been able to find a lookup function that returns a reference. Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you lookup a value & get the cell reference?
Thank you. This does work for column A since the values in both sheets are
the same. Unfortunately, the value I am looking for is text and the text may be anywhere in the text strings in column C so it does not work for column C, but it is better than what I had. Thanks again. "Toppers" wrote: Assuming result is in either column A or C: =IF(ISNA("A"&MATCH(A1,Sheet2!$A$1:$A$10,0)),IF(ISN A("A"&MATCH(A1,Sheet2!$C$1:$C$10,0)),"","C"&MATCH( A1,Sheet2!$C$1:$C$10,0)),"A"&MATCH(A1,Sheet2!$A$1: $A$10,0)) HTH "S. Bevins" wrote: I have a workbook with two sheets. I am trying to look up a value in cell A1 of Sheet 1 in colum A through C of Sheet 2. When it finds the value, I want it to return a reference to the cell where it was found (or somehow tell me where it was found). I could reduce my search to just column A of Sheet 2, but I would prefer not to since there are some cases were the value is in column C. I have not been able to find a lookup function that returns a reference. Is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you lookup a value & get the cell reference?
Try:
=IF(ISNA("A"&MATCH(A1,Sheet2!$A$1:$A$10,0)),IF(ISN A("C"&MATCH("*" & A1 & "*",Sheet2!$C$1:$C$10,0)),"","C"&MATCH("*" & A1 & "*",Sheet2!$C$1:$C$10,0)),"A"&MATCH(A1,Sheet2!$A$1 :$A$10,0)) You could change both searches to be "wildcard" rather thn just the column C HTH "S. Bevins" wrote: Thank you. This does work for column A since the values in both sheets are the same. Unfortunately, the value I am looking for is text and the text may be anywhere in the text strings in column C so it does not work for column C, but it is better than what I had. Thanks again. "Toppers" wrote: Assuming result is in either column A or C: =IF(ISNA("A"&MATCH(A1,Sheet2!$A$1:$A$10,0)),IF(ISN A("A"&MATCH(A1,Sheet2!$C$1:$C$10,0)),"","C"&MATCH( A1,Sheet2!$C$1:$C$10,0)),"A"&MATCH(A1,Sheet2!$A$1: $A$10,0)) HTH "S. Bevins" wrote: I have a workbook with two sheets. I am trying to look up a value in cell A1 of Sheet 1 in colum A through C of Sheet 2. When it finds the value, I want it to return a reference to the cell where it was found (or somehow tell me where it was found). I could reduce my search to just column A of Sheet 2, but I would prefer not to since there are some cases were the value is in column C. I have not been able to find a lookup function that returns a reference. Is this possible? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you lookup a value & get the cell reference?
So close ...
Now it is finding text that is part of other words (e.g., HE in chemistry). I tried to do a "whole word search" by including spaces around the *, but some of the text that I want to find is separated by only a - or / from other text. I think that a case-sensitive search would resolve my dilemma. I tried the FIND function, but that only seems to work if you are searching for text in one cell, not a column. Can perfection be achieved? "Toppers" wrote: Try: =IF(ISNA("A"&MATCH(A1,Sheet2!$A$1:$A$10,0)),IF(ISN A("C"&MATCH("*" & A1 & "*",Sheet2!$C$1:$C$10,0)),"","C"&MATCH("*" & A1 & "*",Sheet2!$C$1:$C$10,0)),"A"&MATCH(A1,Sheet2!$A$1 :$A$10,0)) You could change both searches to be "wildcard" rather thn just the column C HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup a value and return its cell reference instead of the value | Excel Discussion (Misc queries) | |||
Reference Cell Color From Other WorkSheets | Excel Worksheet Functions | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Return cell reference of lookup value | Excel Worksheet Functions |