Array index, match problem

[Guest]

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

 

[Guest]

How about

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

which assumes the colors are in cells G1:G6

 

[Guest]

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

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Alan Beban]

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(dataTable))

Alan Beban

 

[Harlan Grove]

Alan Beban wrote...

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(dataTable))

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(DataTable,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS(DataTable))

 

[Alan Beban]

Alan Beban wrote...



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(DataTable,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

 

[Harlan Grove]

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.

 

[Harlan Grove]

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(DataTable,
ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))

 

[Alan Beban]

Alan Beban wrote...
...


...

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

=INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))

J1 should be J19 to conform to the original specification.

Alan Beban

 

[Alan Beban]

Alan Beban wrote...
...



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

 

[Harlan Grove]

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.

 

[Alan Beban]

Alan Beban wrote...
...



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

 

[Harlan Grove]

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(DataTable,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(dataTable))

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?