2 Dimensional LOOKUP

[vincoviello]

The LOOKUP function in Excel is inadequate for most true
2-dimensional lookups, because it does not take 2 lookup
values (one vertical and one horizontal)as arguments. A
work-around uses VLOOKUP or HLOOKUP and a vector (which
may be hidden) containing the desired column number (with
VLOOKUP) or row number (with HLOOKUP).Each cell in the
vector contains an IF statement like:

=IF(AND((h_lookup_value>=col_or_row_index_value),
(h_lookup_value<next_col_or_row_index_value),2,0)

where 2 is the number for the second column(first after
the index column), (the third column or row, if True
would return a 3, and so forth). Since the resulting
vector will always be all zeroes except for one column or
row number, the 2 dimensional LOOKUP function now becomes:

=VLOOKUP(Vertical_lookup_value,vertical_index_vector,MAX
(created_column_number_vector)

and the converse for an HLOOKUP function.

This workaround should be satisfactory for anyone
desiring a tabular (2-dimensional) LOOKUP until Microsoft
reworks the present LOOKUP function.

Vince Coviello
Adjunct Faculty
Concordia University Austin
(603)494-8139

 

[RagDyer]

What about the INDEX() and MATCH() combination, where MATCH() can be used to
return *both* the column and row arguments?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

The LOOKUP function in Excel is inadequate for most true
2-dimensional lookups, because it does not take 2 lookup
values (one vertical and one horizontal)as arguments. A
work-around uses VLOOKUP or HLOOKUP and a vector (which
may be hidden) containing the desired column number (with
VLOOKUP) or row number (with HLOOKUP).Each cell in the
vector contains an IF statement like:

=IF(AND((h_lookup_value>=col_or_row_index_value),
(h_lookup_value<next_col_or_row_index_value),2,0)

where 2 is the number for the second column(first after
the index column), (the third column or row, if True
would return a 3, and so forth). Since the resulting
vector will always be all zeroes except for one column or
row number, the 2 dimensional LOOKUP function now becomes:

=VLOOKUP(Vertical_lookup_value,vertical_index_vector,MAX
(created_column_number_vector)

and the converse for an HLOOKUP function.

This workaround should be satisfactory for anyone
desiring a tabular (2-dimensional) LOOKUP until Microsoft
reworks the present LOOKUP function.

Vince Coviello
Adjunct Faculty
Concordia University Austin
(603)494-8139

 

[Stephen Dunn]

Are you asking a question, or just making a statement? What's wrong with:

=VLOOKUP(v_lookup_value,table,MATCH(h_lookup_value,OFFSET(table,,,1),0),0)

Untested, but I can't see why it wouldn't work.
Steve D.

 

[Peo Sjoblom]

Maybe he thought he found the grail of multidimensional lookups?

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Stephen Dunn]

Hi Peo,

I'm not quite sure how to take that comment, after all, I know that I'm
guilty of similar ego problems... <g>

Steve D.

Maybe he thought he found the grail of multidimensional lookups?

<snip>