I have 3 sets of data, each with a number and a text description beside it. I need to be able to look up the text description among any of the 3 columns, and return its adjacent value. What complicates it further is that the value is to the left of the text. I've attached a simplified example of what this looks like.
In the example, I need to be able to look up "red" in columns B & E, and it return the value "111".
Does anyone know how to accomplish this with a formula?
Try this one
=IFERROR(INDEX($A$1:$A$3,MATCH(A6,$B$1:$B$3,0)),INDEX($D$1:$D$3,MATCH(A6,$E$1:$E$3,0)))
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Thanks Popipipo.... I am still on Excel 2003 at work. Can you think of how else I can do it without IFERROR?
Maybe this...
=SUMIF(B$1:B$3,A6,A$1:A$3)+SUMIF(E$1:E$3,A6,D$1:D$3)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
or even..Originally Posted by Tony Valko
=SUMIF(B$1:E$3,A6,A$1:D$3)
Life's a spreadsheet, Excel!
Say thanks, Click *
I thought about that but figured if the tables aren't exactly as those in the sample file then that may not work.
I hope this will work=if(iserror(index($a$1:$a$3,match(a6,$b$1:$b$3,0))),index($d$1:$d$3,match(a6,$e$1:$e$3,0)),index($a$1:$a$3,match(a6,$b$1:$b$3,0)))
Tony Valko - I tried yours in my actual tables and it worked
Thanks everyone for your suggestions!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks