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.

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.

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.

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.

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.

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.

MATCH a value in a table
 

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.

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.

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.

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.

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.

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.