| Home |
| Search |
| Today's Posts |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH a value in a table
Thanks, this was helpful and I was able to get it to work.
"Domenic" wrote: The INDEX formula is made up of three arguments -- array, row number, and column number. The array is the range of cells indexed, in this case Sheet1!$A$1:$N$109. The row number is determined by this part of the formula... MIN(IF(Sheet1!$A$1:$N$109=A2,ROW(Sheet1!$A$1:$N $109)-ROW(Sheet1!$A$1)+1))-1 So, for each cell in A1:N109 that equals the value in A2 the corresponding row number is returned. In turn, MIN returns the minimum number. Then, to return the row number for the cell above, we subtract 1 from this number. The column number is determined by this part of the formula... MATCH(A2,INDEX(Sheet1!$A$1:$N$109,MIN(IF(She et1!$A$1:$N$109=A2,ROW(Sheet1!$A$1:$N$109)-ROW(Sheet1!$A$1)+1)),0),0) The INDEX part of the formula provides MATCH with the lookup array. Here, again, MIN returns the first row number in which the value in A2 is found. And since the column number is 0 (the second last 0 in this part of the formula), INDEX returns an array of values for the entire row. So, for example, if the first row that contains the value in A2 is Row 5, the MATCH part of the formula effectively becomes... =MATCH(A2,Sheet1!$A$5:$N$5,0) Hope this helps! In article , Kevin Rodriguez wrote: Can you explain why this works? If I understood this, I might be able to replicate it on my own rather than having to trust the code. "Domenic" wrote: Try... =INDEX(Sheet1!$A$1:$N$109,MIN(IF(Sheet1!$A$1:$N$10 9=A2,ROW(Sheet1!$A$1:$N $109)-ROW(Sheet1!$A$1)+1))-1,MATCH(A2,INDEX(Sheet1!$A$1:$N$109,MIN(IF(She et1!$A$1:$N$109=A2,ROW(Sheet1!$A$1:$N$109)-ROW(Sheet1!$A$1)+1)),0),0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Kevin Rodriguez wrote: I'm trying to find a value within an array, and then offset to a cell above the found cell to get a date. To do this, I'm starting by trying to MATCH a value within an array and getting an #N/A response. My formula is: =MATCH(A2,'Sheet1'!A1:N109,0) A2, the value I'm trying to find, is on Sheet2. The array is 'Sheet1'!A1:N109. I committed by Ctrl-Shift-ENTER. Once I can get this to work, I'll have no problem with the rest. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I link a cell outside a pivot table to one inside the table | Excel Discussion (Misc queries) | |||
| HLOOKUP last match in a table | Excel Worksheet Functions | |||
| Match function in a two input table | Excel Worksheet Functions | |||
| Index & Match on Pivot Table | Excel Discussion (Misc queries) | |||
| Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
Threaded Mode
