Using the Match Function?
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 |
Using the Match Function?
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 |
Using the Match Function?
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... |
Using the Match Function?
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 |
All times are GMT +1. The time now is 11:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com