[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
10
Medium Priority
?
95 Views
Last Modified: 2014-08-08
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
Comment
Question by:tyler43
  • 5
  • 4
10 Comments
 
LVL 15

Expert Comment

by:Phonebuff
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

by:tyler43
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

by:Glenn Ray
ID: 40244224
What if the numeric value exists in more than one column?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:Glenn Ray
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:
=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
0
 

Author Comment

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

Accepted Solution

by:
Glenn Ray earned 800 total points
ID: 40244351
Slightly simpler solution; shortens the COLUMN reference:
=IFERROR(INDEX($A$1:$C$1,1,SUMPRODUCT(($A$1:$C$20=$E$2)*(COLUMN(A:C)))),"Not Found")
0
 

Author Comment

by:tyler43
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

by:Glenn Ray
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

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

Expert Comment

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

-Glenn
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question