Hello, Folks. This is my first post to this Discussion Group. I must have
Dain Bramage to not be able to come up with an answer, but I can't. I need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly appreciated.
Thanks, - Randy

How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I must have
Dain Bramage to not be able to come up with an answer, but I can't. I need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly appreciated.
Thanks, - Randy

Duke,
You are awesome! I haven't even heard of all those commands. Worked like a
charm. Thank you so much. - Randy

"Duke Carey" wrote:

How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I must have
Dain Bramage to not be able to come up with an answer, but I can't. I need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly appreciated.
Thanks, - Randy

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I must
have
Dain Bramage to not be able to come up with an answer, but I can't. I
need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly
appreciated.
Thanks, - Randy

Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I must
have
Dain Bramage to not be able to come up with an answer, but I can't. I
need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly
appreciated.
Thanks, - Randy

Bob,
Thanks a lot for the formula. I will start to disect it and try to learn as
much as I can from it. I appreciate it.
Randy

"Bob Phillips" wrote:

Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I must
have
Dain Bramage to not be able to come up with an answer, but I can't. I
need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly
appreciated.
Thanks, - Randy

Bob,
Thanks again for the formula. I have my app running now, thanks to you. I
would like to ask you another question, but I think it belongs in the
"Programming" board. How would I place your formula in VB script, instead of
inserting it into a cell? Also, the data in the "J19 input cell" is being
placed there (pasted) by a range variable, "X".

Like I said, my app is functioning, but I want to achieve results using more
programming and less cursoring around, like I mentioned before.

Thanks,
Randy


"Bob Phillips" wrote:

Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I must
have
Dain Bramage to not be able to come up with an answer, but I can't. I
need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly
appreciated.
Thanks, - Randy

Randy,

In VBA the code would look like

Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
9) - 1) / 7 + 1)

If you want to use the variable X without going via J19, use

Dim x
x = "H30"
Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
1)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAP" wrote in message
...
Bob,
Thanks again for the formula. I have my app running now, thanks to you.
I
would like to ask you another question, but I think it belongs in the
"Programming" board. How would I place your formula in VB script, instead
of
inserting it into a cell? Also, the data in the "J19 input cell" is being
placed there (pasted) by a range variable, "X".

Like I said, my app is functioning, but I want to achieve results using
more
programming and less cursoring around, like I mentioned before.

Thanks,
Randy


"Bob Phillips" wrote:

Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I
must
have
Dain Bramage to not be able to come up with an answer, but I can't.
I
need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table
and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly
appreciated.
Thanks, - Randy

Bob,
Once again, thanks. Works great. Now, how do I get the result of the
formula (Red, White, etc...) pasted into yet another cell? I still have the
mind-set that the result is in a cell somewhere. As soon as I can start
thinking "programmatically", these simple questions will, well, at least they
should diminish.

Thanks again,
Randy

"Bob Phillips" wrote:

Randy,

In VBA the code would look like

Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
9) - 1) / 7 + 1)

If you want to use the variable X without going via J19, use

Dim x
x = "H30"
Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
1)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAP" wrote in message
...
Bob,
Thanks again for the formula. I have my app running now, thanks to you.
I
would like to ask you another question, but I think it belongs in the
"Programming" board. How would I place your formula in VB script, instead
of
inserting it into a cell? Also, the data in the "J19 input cell" is being
placed there (pasted) by a range variable, "X".

Like I said, my app is functioning, but I want to achieve results using
more
programming and less cursoring around, like I mentioned before.

Thanks,
Randy


"Bob Phillips" wrote:

Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group. I
must
have
Dain Bramage to not be able to come up with an answer, but I can't.
I
need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell J20.
I need a formula that will match value H26 from the following table
and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly
appreciated.
Thanks, - Randy

Hi again Randy,

That would be a single assignment to a cell, say M2, and assuming variable X
has that lookup value

Range("M2").Value = Application.Index(Range("$G$1:$G$6"), (Mid(X, 2, 9) - 1)
/ 7 + 1)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAP" wrote in message
...
Bob,
Once again, thanks. Works great. Now, how do I get the result of the
formula (Red, White, etc...) pasted into yet another cell? I still have
the
mind-set that the result is in a cell somewhere. As soon as I can start
thinking "programmatically", these simple questions will, well, at least
they
should diminish.

Thanks again,
Randy

"Bob Phillips" wrote:

Randy,

In VBA the code would look like

Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"),
2,
9) - 1) / 7 + 1)

If you want to use the variable X without going via J19, use

Dim x
x = "H30"
Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) /
7 +
1)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAP" wrote in message
...
Bob,
Thanks again for the formula. I have my app running now, thanks to
you.
I
would like to ask you another question, but I think it belongs in the
"Programming" board. How would I place your formula in VB script,
instead
of
inserting it into a cell? Also, the data in the "J19 input cell" is
being
placed there (pasted) by a range variable, "X".

Like I said, my app is functioning, but I want to achieve results
using
more
programming and less cursoring around, like I mentioned before.

Thanks,
Randy


"Bob Phillips" wrote:

Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Duke Carey" wrote in message
...
How about

=INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H ",""))/7,0))

which assumes the colors are in cells G1:G6



"RAP" wrote:

Hello, Folks. This is my first post to this Discussion Group.
I
must
have
Dain Bramage to not be able to come up with an answer, but I
can't.
I
need
some help.
Below is an example of my problem.
Input cell = J19. Input Value = H26. Formula placed in cell
J20.
I need a formula that will match value H26 from the following
table
and
return "Green" as the result.


H2 H3 H4 H5 H6 H7 Red
H9 H10 H11 H12 H13 H14 White
H16 H17 H18 H19 H20 H21 Blue
H23 H24 H25 H26 H27 H28 Green
H30 H31 H32 H33 H34 H35 Black
H37 H38 H39 H40 H41 H42 Purple

Any help, pointers, suggestions or direction will be greatly
appreciated.
Thanks, - Randy

Bob Phillips wrote:
Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

For a formula that is independent of the size of the table of data or
the regularity of its contents, if the functions in the freely
downloadable file at http://home.pacbell.net are available to your workbook

=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataT able))

Alan Beban

Alan Beban wrote...
Bob Phillips wrote:
Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

For a formula that is independent of the size of the table of data or
the regularity of its contents, if the functions in the freely
downloadable file at http://home.pacbell.net are available to your workbook

=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0 ,
-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataT able))

Assuming someone would use your function library, wouldn't they want to
do so efficiently? Only one udf call needed (MakeArray).

=INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTabl e,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS(DataTable))

Harlan Grove wrote:
Alan Beban wrote...

Bob Phillips wrote:

Just for interest, two less functions

=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

For a formula that is independent of the size of the table of data or
the regularity of its contents, if the functions in the freely
downloadable file at http://home.pacbell.net are available to your workbook

=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")), 0,
-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataT able))


Assuming someone would use your function library, wouldn't they want to
do so efficiently? Only one udf call needed (MakeArray).

=INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTabl e,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS(DataTable))

Well, isn't that interesting! I would have thought the suggestion would
be something like fill down a range on Sheet8, for example, named CxRV with

=INDIRECT(ADDRESS(
ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
(compliments of Chip Pearson)

and then enter something like

=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

in order to use only built-in functions and avoid at all costs the
dreaded Array Functions.

But no, here's Harlan Grove, instead carping about which of the dreaded
Array Functions is more efficient. Onward and upward!

Alan Beban

Alan Beban wrote...
....
But no, here's Harlan Grove, instead carping about which of the dreaded
Array Functions is more efficient. Onward and upward!

Just pointing out that you don't know how to use your own function
library efficiently.

Onward perhaps. Never upward.

Alan Beban wrote...
....
=INDIRECT(ADDRESS(
ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
....
=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

in order to use only built-in functions and avoid at all costs the
dreaded Array Functions.
....

Ugh! Not the best way by a long shot! All it takes is a single array
formula

=INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTab le,
ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)0,0),COLUMNS(DataT able))

Harlan Grove wrote:
Alan Beban wrote...
...

=INDIRECT(ADDRESS(
ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

...

=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

in order to use only built-in functions and avoid at all costs the
dreaded Array Functions.

...

Ugh! Not the best way by a long shot! All it takes is a single array
formula

=INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTab le,
ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)0,0),COLUMNS(DataT able))


J1 should be J19 to conform to the original specification.

Alan Beban

Harlan Grove wrote:
Alan Beban wrote...
...

But no, here's Harlan Grove, instead carping about which of the dreaded
Array Functions is more efficient. Onward and upward!


Just pointing out that you don't know how to use your own function
library efficiently.


Perhaps you could quantify for the users the difference in efficiency.

Alan Beban

Alan Beban wrote...
....
Perhaps you could quantify for the users the difference in efficiency.

Nah, I'll leave that for you as an exercise since you're the one who
needs to learn about efficiency. Here's a hint: one udf call will
invariably be faster than two, even when there are a few extra built-in
function calls with the single udf call.

Harlan Grove wrote:
Alan Beban wrote...
...

Perhaps you could quantify for the users the difference in efficiency.


Nah, I'll leave that for you as an exercise since you're the one who
needs to learn about efficiency.

Cute. But I suspect the real reason you don't want to deal with it is
that the so-called "efficiency" to which you and many
programmer/developers sometimes refer often involves nanoseconds of
difference that are totally irrelevant to most users in most
applications; interesting to you for purposes of posting oneupmanship,
but somewhat misleading for users generally.

Alan Beban

Alan Beban wrote...
..=2E.
Cute. But I suspect the real reason you don't want to deal with it is
that the so-called "efficiency" to which you and many
programmer/developers sometimes refer often involves nanoseconds of
difference that are totally irrelevant to most users in most
applications; interesting to you for purposes of posting oneupmanship,
but somewhat misleading for users generally.

Fine. Then consider whether the MakeArray formula,

=3DINDEX(DataTable,INT((MATCH(J1=AD9,MakeArray(Dat aTable,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS=AD(DataTable))

a single MATCH against the data range transformed into a 1D array, with
the result adjusted by a division inside INT to return the row number,
against the ArrayMatch formula,

=3DOFFSET(INDIRECT(ArrayMatch(J=AD19,dataTable,"A" )),0,
-INDEX(ArrayMatch(J19,dataTab=ADle),1,2)+COLUMNS(da taTable))

first returning the cell address of the matching cell then using
another call to fix the column offset. It's subjective whether the row
index contortions of the MakeArray formula are more obscure than the
column offset contortions of the 2 ArrayMatch formula.

For that matter, you could also have used

=3DINDEX(DataTable,INDEX(ArrayMatch(J19,DataTable) ,1),COLUMNS(DataTable))

which would have been a LOT simpler than either of the others.
Simplicity is good.

Both the MakeArray and the single ArrayMatch formulas involve no
volatile function calls, so they won't cause Excel to prompt users to
save any file containing them if users try to close such workbooks
without making any changes. Your two ArrayMatch formula, due to OFFSET
and INDIRECT calls, would cause such confusing prompts.

Is that an acceptable user consideration?

Harlan Grove wrote:
Alan Beban wrote...
...

Cute. But I suspect the real reason you don't want to deal with it is
that the so-called "efficiency" to which you and many
programmer/developers sometimes refer often involves nanoseconds of
difference that are totally irrelevant to most users in most
applications; interesting to you for purposes of posting oneupmanship,
but somewhat misleading for users generally.


Fine. Then consider whether the MakeArray formula,

=INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTab le,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS*(DataTable))

a single MATCH against the data range transformed into a 1D array, with
the result adjusted by a division inside INT to return the row number,
against the ArrayMatch formula,

=OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0 ,
-INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(data Table))

first returning the cell address of the matching cell then using
another call to fix the column offset. It's subjective whether the row
index contortions of the MakeArray formula are more obscure than the
column offset contortions of the 2 ArrayMatch formula.

For that matter, you could also have used

=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1 ),COLUMNS(DataTable))

which would have been a LOT simpler than either of the others.
Simplicity is good.

Both the MakeArray and the single ArrayMatch formulas involve no
volatile function calls, so they won't cause Excel to prompt users to
save any file containing them if users try to close such workbooks
without making any changes. Your two ArrayMatch formula, due to OFFSET
and INDIRECT calls, would cause such confusing prompts.

Is that an acceptable user consideration?

It's certainly more constructive, particularly the suggestion of

=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1 ),COLUMNS(DataTable))

which is significantly faster than the one including the two ArrayMatch
function calls that I originally posted. Would have been nice had you
focused on the instructional value for the users in the first place,
rather than just on stroking your ego. But then, there you go!

By the way, the formula with the MakeArray function call seems to return
an error if the data table exceeds 65536 elements (I haven't yet
identified why; it might be fixable), while neither of the ArrayMatch
formulas seems to--though they are slower.

Alan Beban

Alan Beban wrote:
Harlan Grove wrote:
. . .Then consider whether the MakeArray formula,

=INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTab le,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS*(DataTable))

a single MATCH against the data range transformed into a 1D array, with
the result adjusted by a division inside INT to return the row number,
against the ArrayMatch formula,

=OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0 ,
-INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(data Table))

first returning the cell address of the matching cell then using
another call to fix the column offset. It's subjective whether the row
index contortions of the MakeArray formula are more obscure than the
column offset contortions of the 2 ArrayMatch formula.

...By the way, the formula with the MakeArray function call seems to return
an error if the data table exceeds 65536 elements . . . .

The problem is with the built-in INDEX function; it fails if the array
or reference contains more than 65536 elements.

Alan Beban

Alan Beban wrote...
....
The problem is with the built-in INDEX function; it fails if the array
or reference contains more than 65536 elements.

Are you sure there isn't a problem with 65,536 elements? I'd suspect it
chokes after 65,535 elements.

Which would argue in favor of using the COUNTIF function provided in
another branch of this thread. It's one drawback is the volatile OFFSET
call. It'd always recalc, but it'd be lots faster than even a single
udf call.

Alan Beban wrote...
....
=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable), 1),COLUMNS(DataTable))

which is significantly faster than the one including the two ArrayMatch
function calls that I originally posted. Would have been nice had you
focused on the instructional value for the users in the first place,
rather than just on stroking your ego. But then, there you go!

Of course you could have offerred it too, if you had thought of it. But
then, there you go!

By the way, the formula with the MakeArray function call seems to return
an error if the data table exceeds 65536 elements (I haven't yet
identified why; it might be fixable), while neither of the ArrayMatch
formulas seems to--though they are slower.

Excel can't handle any arrays with more than 65,535 entries in either
of 1 or 2 dimensions, as you should know. It's questionable whether
anyone should try to use brute force matching on so many cells. It'd be
slow even without any udfs or volatile functions. There are tasks for
which indexed database searches would be far more appropriate than
unindexed spreadsheet searches. If the OP has so many entries to
search, the OP is being foolish using a spreadsheet for the task.
However, if the OP is only searching a few hundred entries or fewer,
your caveat provides completeness of specification but is of no
practical relevance.

Harlan Grove wrote:
Alan Beban wrote...
...

The problem is with the built-in INDEX function; it fails if the array
or reference contains more than 65536 elements.


Are you sure there isn't a problem with 65,536 elements? I'd suspect it
chokes after 65,535 elements. . . .

Dim arr, i
ReDim arr(1 To 65536)
For i = 1 To 65536
arr(i) = i * 2
Next
MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

In xl2000 and earlier it fails on 5462 elements or greater.

Alan Beban

Alan Beban wrote:
Harlan Grove wrote:

Alan Beban wrote...
...

The problem is with the built-in INDEX function; it fails if the array
or reference contains more than 65536 elements.



Are you sure there isn't a problem with 65,536 elements? I'd suspect it
chokes after 65,535 elements. . . .


Dim arr, i
ReDim arr(1 To 65536)
For i = 1 To 65536
arr(i) = i * 2
Next
MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

In xl2000 and earlier it fails on 5462 elements or greater.

Alan Beban

Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
xl2000 or xl2002).

Alan Beban

Alan Beban wrote...
Are you sure there isn't a problem with 65,536 elements? I'd suspect it
chokes after 65,535 elements. . . .

Dim arr, i
ReDim arr(1 To 65536)
For i = 1 To 65536
arr(i) = i * 2
Next
MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

In xl2000 and earlier it fails on 5462 elements or greater.

Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
xl2000 or xl2002).

Not comparable. A:B is a range, not an array. But you're correct that
INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
subsequent arguments and converts them to long integers.

Harlan Grove wrote:
Alan Beban wrote...

Are you sure there isn't a problem with 65,536 elements? I'd suspect it
chokes after 65,535 elements. . . .

Dim arr, i
ReDim arr(1 To 65536)
For i = 1 To 65536
arr(i) = i * 2
Next
MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

In xl2000 and earlier it fails on 5462 elements or greater.

Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
xl2000 or xl2002).


Not comparable. A:B is a range, not an array. But you're correct that
INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
subsequent arguments and converts them to long integers.


It seems that the limitation on the VBA invocation of the INDEX function
is a bit subtler; it is not limited by the number of the elements in the
array (see arr1 below) but apparently by the number of elements in a
dimension.

Sub testIt3a()
Dim arr1, arr2, arr3
Dim i As Long, j As Long
Dim x, y, z

'65536 rows, 2 columns
ReDim arr1(1 To 65536, 1 To 2)
For i = 1 To 65536: For j = 1 To 2
arr1(i, j) = i * 2 + j
Next: Next

'1 row, 65536 columns
ReDim arr2(1 To 65536)
For i = 1 To 65536
arr2(i) = i
Next

'1 row, 65537 columns
ReDim arr3(1 To 65537)
For i = 1 To 65537
arr3(i) = i
Next

x = Application.Index(arr1, 65536, 2)
Debug.Print x '<---returns 131074
y = Application.Index(arr2, 65536)
Debug.Print y '<---returns 65536
z = Application.Index(arr3, 65536) 'Type mismatch error
End Sub

Alan Beban