simultaneous v&h lookup - approaches...
 

AFAIK, Excel has no function allowing simultaneous search for 1 value
along leftmost column of a table range and then also search
horizontally across table top row.

1 way to do this is to embed a LOOKUP to figure out how many columns or
rows to index over (in case of VLOOKUP/HLOOKUP respectively).

Anything else?

One way is with
INDEX(WholeDataRange,MATCH(xyz,Column_Data,0),MATC H(xyz,Row_Data,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

wrote in message
ups.com...
AFAIK, Excel has no function allowing simultaneous search for 1 value
along leftmost column of a table range and then also search
horizontally across table top row.

1 way to do this is to embed a LOOKUP to figure out how many columns or
rows to index over (in case of VLOOKUP/HLOOKUP respectively).

Anything else?

tripp

Don't know if this would help.......

If you have your rows and columns named you can use the intersection operation
to find a value.

Or if you have labels, say across row 1 and down column A you can use them to
create an intersection table.

i.e.

A2:E5 have data.

B1:E1 have title1, title2, title3, title4

A2:A5 have item1, item2, item3, item4

Select A1:E5

InsertNameCreate. Check top row and left column.

Now in H4 enter =title2 item3

Works great for mileage charts and similar operations but might not be
applicable for your needs.


Gord Dibben Excel MVP

On 11 Feb 2005 13:31:19 -0800, wrote:

AFAIK, Excel has no function allowing simultaneous search for 1 value
along leftmost column of a table range and then also search
horizontally across table top row.

1 way to do this is to embed a LOOKUP to figure out how many columns or
rows to index over (in case of VLOOKUP/HLOOKUP respectively).

Anything else?

Index + Match is probably the fastest:
See Ken's example

Array Sum is probably the shortest:
=SUM((A6=A2:A4)*(A7=B1:F1)*(B2:F4))
Confirm the formula with Ctrl+Shift and Enter.

Unless you use the intersection approach:
See http://www.dicks-blog.com/archives/2...and-intersect/
or Gordon's example.


Ola Sandstrom

That second xyz should really have been something else, as you likely
wouldn't use the same variable in both places.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" wrote in message
...
One way is with
INDEX(WholeDataRange,MATCH(xyz,Column_Data,0),MATC H(xyz,Row_Data,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--

wrote in message
ups.com...
AFAIK, Excel has no function allowing simultaneous search for 1 value
along leftmost column of a table range and then also search
horizontally across table top row.

1 way to do this is to embed a LOOKUP to figure out how many columns or
rows to index over (in case of VLOOKUP/HLOOKUP respectively).

Anything else?