Link to home
Start Free TrialLog in
Avatar of tyler43
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.
Avatar of Phonebuff
Phonebuff
Flag of United States of America image

Have you looked at the function vlookup() and hlookup()

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


============
Avatar of tyler43
tyler43

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".
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,SUMPRODUCT(($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
Avatar of tyler43

ASKER

Numeric values are totally unique and don't repeat in this specif situation.
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tyler43

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!
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
Avatar of tyler43

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