Search Table, Display Column Header

**mattalisch** · 08-10-2009, 11:49 AM

Guys,

I'm pretty new to excel, and am trying to accomplish something that should be pretty easy. Basically, I have a table that has row headers to the left, column headers on top, and all the row data increases left to right.

I'm trying to write a function that will automatically find the correct row header (that matches a previously calculated cell), search across the row to find a fixed value "x", then go to the top of the column and display the column header.

The end result of this is that the proper column header is displayed for a given row input and table value.

I couldn't figure it out with VLOOKUP, INDEX, OFFSET, MATCH, etc. but I know there are smarter folks out there. Any help would be appreciated, thanks!

**ChemistB** · 08-10-2009, 12:21 PM

Assuming that you will get an exact match in your row headers and in your table (otherwise you can build a lot of IF functions into this);
Setting A1 to your row header you are looking for
B1 to your table entry you are looking for
B2:I2 is column headers (final answer)
A3:A32 is your row headers

Please Login or Register  to view this content.

See attachment below.

Edit: The OFFSET function is volatile so this may slow down your calculations if your spreadsheet is complex. DONKEY OTE is the master of replacing volatile Functions but I don't see how this one would easily be replaced. He might know.

**mattalisch** · 08-10-2009, 02:08 PM

Chemist,

I don't exactly understand it yet, but it seems to work! Thanks again for the quick reply, that was awesome!

MA

**DonkeyOte** · 08-12-2009, 02:42 PM

Hey, I only just ran a quick search to see if I'd been slated anywhere (I wasn't already aware of!) and came across this and yes you could avoid OFFSET here by using INDEX in such a way as to return all columns for a given row... ie this:

Please Login or Register  to view this content.

could become

Please Login or Register  to view this content.

So you run the MATCH of value against a range generated by the INDEX which itself is determined by the MATCH of value 1 against the left hand side column - by setting the column to 0 in the INDEX the range returns all values in that row (ie all columns)... because the final range is a Vector (1 row) you don't actually need to specify both column & row flag you need only specify that which is variable
(ie for a vertical vector column will always be 1 and for a horizontal vector the opposite is true in so far as the Row will always be 1)

EDIT: P.S. Flattery gets you everywhere... I don't think I've been referred to as the Master of anything other than of perhaps "disaster"...