tyler43
asked on
Look up a corresponding text value based on a unique number in a range of numbers
I have a range of unique numbers that say are located in the range A2:D100, and corresponding text values are in the header of the range in A1:D1. What's the best way to return the text value based on the unique number. I realized I can't do it with SUMPRODUCT lookup since sumproduct lookups don't return text values.
ASKER
To illustrate:
Dog Cat Mouse
0.2570 0.5804 0.9788
0.6996 0.3740 0.7969
0.8522 0.4584 0.6056
0.4455 0.5940 0.0239
0.9675 0.0059 0.8557
0.8891 0.9361 0.3796
0.3450 0.7439 0.4690
0.9793 0.0408 0.6457
I need to return text from the first row based on a unique number within a range, so for 0.5940 lookup will return "Cat".
Dog Cat Mouse
0.2570 0.5804 0.9788
0.6996 0.3740 0.7969
0.8522 0.4584 0.6056
0.4455 0.5940 0.0239
0.9675 0.0059 0.8557
0.8891 0.9361 0.3796
0.3450 0.7439 0.4690
0.9793 0.0408 0.6457
I need to return text from the first row based on a unique number within a range, so for 0.5940 lookup will return "Cat".
What if the numeric value exists in more than one column?
Here is a solution that will return the column name if unique values exist.
If your data range is in columns A:C and the value you are looking for is in cell E2, enter this formula:
=IFERROR(INDEX($A$1:$C$1,1 ,SUMPRODUC T(($A$1:$C $20=$E$2)* (COLUMN($A $1:$C$20)) )),"Not Found")
I only extended this to 20 rows; you can change that value as appropriate.
See example workbook.
-Glenn
EE-IdentifyColumn2.xlsx
If your data range is in columns A:C and the value you are looking for is in cell E2, enter this formula:
=IFERROR(INDEX($A$1:$C$1,1
I only extended this to 20 rows; you can change that value as appropriate.
See example workbook.
-Glenn
EE-IdentifyColumn2.xlsx
ASKER
Numeric values are totally unique and don't repeat in this specif situation.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for tyler43's comment #a40244251
for the following reason:
Works like a charm! Thanks!
Accepted answer: 0 points for tyler43's comment #a40244251
for the following reason:
Works like a charm! Thanks!
Hi,
If my answer was acceptible, can you please click the "Accept this solution" above my previous post with the correct solution? That will properly close the question and make it available to other EE users.
Thanks,
Glenn
If my answer was acceptible, can you please click the "Accept this solution" above my previous post with the correct solution? That will properly close the question and make it available to other EE users.
Thanks,
Glenn
ASKER
sorry I think I did something wrong initially while trying to close the question
Thanks for accepting my solution; I'm glad I was able to help.
-Glenn
-Glenn
Not sure I understand your question, but I suspect one or the other of these functions will do what you want --
http://www.techonthenet.com/excel/formulas/hlookup.php
============