Hello,
I'm trying to use the Match function to find a value in a large
table. I am able to get the match function to work looking in 1 column
or 1 row but using it to search more then 1 column or row doesn't work.
Basically what I want to happen is to have Match return the column #
in which the value resides so that I can use it in the Index function
to return the Column heading.

The Table has 49 rows and 49 cells. Anyone have any ideas or thoughts.
Here is what I have so far.

MATCH(C5,Table,0)

I keep getting a return of #NA

Thanks,
Jeff

Hi Jeff,
When I use Index(Match) together I think of the formula this way:

=INDEX(Range to Search, ?, ?) where neither column or row are known and
therefore, ? becomes MATCH(Cell to Match, Range in which to search for match,
FALSE)

All together you get:

=INDEX(Range to Search,MATCH(Cell to Match, Range in which to search for
match, FALSE),MATCH(Cell to Match, Range in which to search for match, FALSE)

However, this will only get you a single cell and not the column heading.

I hope this helps.
-Nicole


" wrote:

Hello,
I'm trying to use the Match function to find a value in a large
table. I am able to get the match function to work looking in 1 column
or 1 row but using it to search more then 1 column or row doesn't work.
Basically what I want to happen is to have Match return the column #
in which the value resides so that I can use it in the Index function
to return the Column heading.

The Table has 49 rows and 49 cells. Anyone have any ideas or thoughts.
Here is what I have so far.

MATCH(C5,Table,0)

I keep getting a return of #NA

Thanks,
Jeff

To my knowledge MATCH works with an array and not with a range. MATCH Arrays
must have only one dimension which is why you get a N/A if you use a
multi-column range for the 2nd argument.

I am not sure how you can traverse a range in a match statement one column
(or row) at a time so that MATCH works. I would do it in code.

If I am wrong on the above please correct me. I have run into a similar
situation in the past and I only managed to make MATCH work with a single
column range.

I am also not sure how Nicole uses her approach. Nicole please explain...

Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xlFunctions03.html

Take a look at example 3. She uses index() to get the first row/first column of
a table.

" wrote:

Hello,
I'm trying to use the Match function to find a value in a large
table. I am able to get the match function to work looking in 1 column
or 1 row but using it to search more then 1 column or row doesn't work.
Basically what I want to happen is to have Match return the column #
in which the value resides so that I can use it in the Index function
to return the Column heading.

The Table has 49 rows and 49 cells. Anyone have any ideas or thoughts.
Here is what I have so far.

MATCH(C5,Table,0)

I keep getting a return of #NA

Thanks,
Jeff

--

Dave Peterson