How to search an interior matrix/array and lookup a corresponding value in Excel?

July 21, 2006 1:26 PM

Excel Powers Active! Vague Excel spreadsheet lookup problem inside.

On preview, I apologize for the messy table below but it should make enough sense. I've got a fairly large Excel table that looks something like this:

     ColA            ColB              ColC                  ColD
1   Index1        Moretext       Evenmore          Don't_care...
2   Index2        Stilldifferent  Veryfrustarting  Don't_care...
3   .....
4   .....

What I would like to do is provide a search key, that should appear in Column B or Column C. And what gets returned is the value in Column A. So if I searched for 'Evenmore' I would get 'Index1' and if I searched for 'Stilldifferent' I would get 'Index2'.

To complicate things, I don't want to search beyond column C.

In reality, I actually have different strings in Columns B to I, and the value I want to look up is in Column A, J, K, or L. I have just simplified the diagram above for your comprehension pleasure. The number of rows is 255, but this could grow. Thanks in advance!

posted by brettcar to computers & internet (8 comments total)

Jaysus. You're trying to use excel like a database. Use a database instead. You'll save yourself a lot of headaches.

But, if you insist: In the Excel functions, under the "Database" category, there's a function DGET which will do what you want.
posted by orthogonality at 1:51 PM on July 21, 2006

I need some clarification. How will you be "searching"? Are you trying to bring this information into another worksheet? Or are you simply using Excel's Find, typing "Evenmore" and hoping for it to take you to "Index1"?
posted by bwilms at 1:52 PM on July 21, 2006

I did a quick and dirty formula to do what I think you want using only the sample table. I'm at home on my Mac where my Excel speed is not wicked fast, so I didn't try to make it expandable.

Anyway, here it is:

=IF(ISNUMBER(MATCH(B6,$B$1:$B$2,0)),INDEX($A$1:$D$2,MATCH(B6,$B$1:$B$2,0),1),INDEX($A$1:$D$2,MATCH(B6,$C$1:$C$2,0),1))

Where B6=the cell you input your search term. I had the "output" in cell C6. This will work if all you want to do is put in a search term and spit out the result from colA.
posted by mullacc at 1:57 PM on July 21, 2006

Like ortho said, Excel is probably not the program to use. But I'm a finance guy, not a programmer or database guy, and Excel is all I know.
posted by mullacc at 2:02 PM on July 21, 2006

Yeah, a database would be better suited for this application. I will consider that, but this is such a one-off application that I wanted to use the tool I had most available.

I was hoping to do something with VLOOKUP or HLOOKUP, from another Excel worksheet/workbook, but I will try what mullacc suggested for now. Thanks!
posted by brettcar at 3:04 PM on July 21, 2006

The LOOKUP functions will handle this example. I use them quite a bit when I'm preparing financial models. The only consideration is that the list needs to be sorted in ascending order.
posted by michswiss at 3:20 PM on July 21, 2006

The simplest thing to do is to stick your results column (or a copy of it) to the right of the things you're looking for. Then it's a simple =vlookup(searchterm,tablename,colum_index,0).

To cater for two columns, you could wrap your vlookup up in some 'if's and 'isna's... though I just bet I get the brackets wrong here because I don't have excel handy....

So say you've moved copied the result field from column A to column E, you'd have:

=if(isna(vlookup(searchterm,B:E,4,0)),(if(isna(vlookup(searchterm,C:E,3,0)),"Not Found",vlookup(searchterm,C:E,3,0)),vlookup(searchterm,B:E,4,0)))
posted by pompomtom at 4:53 PM on July 21, 2006

I've always used the combination of INDEX() and MATCH() when I didn't want to re-arrange a table in order to use the LOOKUP() functions. Plus I find it easier to copy the formula and alter the arrays (by hitting F2 and dragging the reference box with my mouse) rather than worry about the column number hard-plugged into the LOOKUP() function.
posted by mullacc at 7:52 PM on July 21, 2006