| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH a value in a table
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
=MATCH(A2,'Sheet1'!A1:N109,0)-1
no need to do ctl-shift-enter. Just enter "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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
It didn't work. Why did you include the -1 after the formula?
"Teethless mama" wrote: =MATCH(A2,'Sheet1'!A1:N109,0)-1 no need to do ctl-shift-enter. Just enter "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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
see if these ideas help
Sub findem() x = Range("a12:e21").Find(1111).Offset(-1) MsgBox x End Sub Function fd(x, y) fd = Range(x).Find(y).Offset(-1) End Function -- Don Guillett SalesAid Software "Kevin Rodriguez" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
It's been several years since I last used Subs and Functions. I've entered
this using the VB Editor. Now what? "Don Guillett" wrote: see if these ideas help Sub findem() x = Range("a12:e21").Find(1111).Offset(-1) MsgBox x End Sub Function fd(x, y) fd = Range(x).Find(y).Offset(-1) End Function -- Don Guillett SalesAid Software "Kevin Rodriguez" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
OK, I tried this without success.
My function is: Public Function fd(s, x, y) Application.Volatile fd = Worksheets(s).Range(x).Find(y).Offset(-1) End Function The cell in my sheet is: =fd("Master Field Allocation", "A1:N109",A2) For some reason it looks to be treating the formula as a text value and not performing a calculation. to find the value. "Don Guillett" wrote: Changed to allow for anoter sheet. Put both in a REGULAR module (not sheet or ThisWorkbook). To use the first use f5 while cursor is on the macro or if on the worksheet alt f8 to see or assign to a button. Sub findem() x = Sheets("b").Range("a12:e21").Find(1111).Offset(-1) MsgBox x End Sub To use this like any function, type =fd("yoursheet","yourrnage",number to find) =fd("sheet1","a12:e222",1111) Function fd(s, x, y) application.volatile fd = Sheets(s).Range(x).Find(y).Offset(-1) End Function -- Don Guillett SalesAid Software "Kevin Rodriguez" wrote in message ... It's been several years since I last used Subs and Functions. I've entered this using the VB Editor. Now what? "Don Guillett" wrote: see if these ideas help Sub findem() x = Range("a12:e21").Find(1111).Offset(-1) MsgBox x End Sub Function fd(x, y) fd = Range(x).Find(y).Offset(-1) End Function -- Don Guillett SalesAid Software "Kevin Rodriguez" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
I think I'm out of my league here. I can't even break down your statement to
understand how all the nested functions relate to each other. I entered this into my sheet and it worked the first time, but it came up as an array iof three cells. I haven't been able to get it to work since and occupy only one cell. "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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MATCH a value in a table
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. |
|
#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. |
| Reply |
| 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) | |||
Hybrid Mode
