Using Lookup in an Array

Is there a way to lookup a value in an array covering several columns using a formula?

In the example below I would like to enter a call number in the yellow cell, have the sheet search the green area and then return the "type" and "color" in the blue cells. I manually populated the blue cells with light grey text simply for representation of what I am looking for as a result. Seems like this could be done with lookup searching for an exact match, but it does not.

I have also attached the actual sheet for the example as well.

Any help would be greatly appreciated.


ex1.jpg

Hey Jeremy -

I saw your problem and it reminded me of something similiar I have been working on. I found a similar problem on another post on this website (link below). With some slight adaptation, you can make this work for you.

For the purposes of this presentation, I will call the full range (A7:X10) or whatever, "DataRange" and the Range containing the target Call # as "TargetCell"
In this case, your grey box formula would be
Type: =OFFSET(INDIRECT(ADDRESS(SUMPRODUCT(("DataRange"="TargetCell")*ROW("DataRange")),SUMPRODUCT(("DataRange"="TargetCell")*COLUMN("DataRange")),4)),0,1)
Type: =OFFSET(INDIRECT(ADDRESS(SUMPRODUCT(("DataRange"="TargetCell")*ROW("DataRange")),SUMPRODUCT(("DataRange"="TargetCell")*COLUMN("DataRange")),4)),0,2)

The post containing the meat of this solution is at: http://www.mrexcel.com/forum/excel-q...ire-sheet.html

Awesome! I was able to get both to work with some slight adjustments. I do have a question though...

@ndsutherland: How do I remove the border that appears with your formula?

Thank you ndsutherland and Bstack! Now I can continue...

haha... Nevermind ndsutherland...I just tried the "no borders" on the column and it worked fine. For some reason I assumed the formula was incorporating it. Doh!