Look up a value from multiple columns and return an adjacent value

[abaginska09]

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?

[popipipo]

Please Login or Register  to view this content.

Try this one

[abaginska09]

Thanks Popipipo.... I am still on Excel 2003 at work. Can you think of how else I can do it without IFERROR?

[Tony Valko]

Maybe this...

=SUMIF(B$1:B$3,A6,A$1:A$3)+SUMIF(E$1:E$3,A6,D$1:D$3)

[Ace_XL]

Maybe this...

=SUMIF(B$1:B$3,A6,A$1:A$3)+SUMIF(E$1:E$3,A6,D$1:D$3)

or even..
=SUMIF(B$1:E$3,A6,A$1:D$3)

[popipipo]

Please Login or Register  to view this content.

I hope this will work

[Tony Valko]

I thought about that but figured if the tables aren't exactly as those in the sample file then that may not work.

[abaginska09]

Tony Valko - I tried yours in my actual tables and it worked

Thanks everyone for your suggestions!

[Tony Valko]

You're welcome. Thanks for the feedback!