simultaneous v&h lookup - approaches...

[trippknightly]

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?

 

[Ken Wright]

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

 

[Gord Dibben]

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

Insert>Name>Create. 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

 

[Guest]

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/2005/01/16/union-and-intersect/
or Gordon's example.


Ola Sandstrom

 

[Ken Wright]

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