[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Look up a corresponding text value based on a unique number in a range of numbers

Posted on 2014-08-06
Medium Priority
95 Views
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.
0
Question by:tyler43
• 5
• 4

LVL 15

Expert Comment

ID: 40244150
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

============
0

Author Comment

ID: 40244198
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".
0

LVL 27

Expert Comment

ID: 40244224
What if the numeric value exists in more than one column?
0

LVL 27

Expert Comment

ID: 40244249
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:

I only extended this to 20 rows; you can change that value as appropriate.

See example workbook.

-Glenn
EE-IdentifyColumn2.xlsx
0

Author Comment

ID: 40244251
Numeric values are totally unique and don't repeat in this specif situation.
0

LVL 27

Accepted Solution

Glenn Ray earned 800 total points
ID: 40244351
Slightly simpler solution; shortens the COLUMN reference:
0

Author Comment

ID: 40244969
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!
0

LVL 27

Expert Comment

ID: 40244970
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
0

Author Comment

ID: 40248979
sorry I think I did something wrong initially while trying to close the question
0

LVL 27

Expert Comment

ID: 40248989
Thanks for accepting my solution; I'm glad I was able to help.

-Glenn
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month18 days, 2 hours left to enroll