Why isnt there an easy way???

  • Thread starter Thread starter twm145
  • Start date Start date
T

twm145

Why is'nt there an easy way for excell to lookup a value in a table based on
two variables?
This is not an uncommon format.

Hat Coat Sweater Shoe Tie
A 1 2 3 4 5
B 6 7 8 9 10
C 11 12 13 14 15


You'd think you could just pass the two arguments: SimpleLookup(B, Shoe,
ArrayName) and get the answer, 9
Is there something I'm missing here?
Why must this be made difficult?
 
There IS an easy way.
You can use the space (range intersection) operator.
The formula:
=hat b
will return the value 6.
You don't even need to name the ranges, Excel works out what you meant.
 
With your Column A,B,C in Column A and Hat in Cell B1:
J1 = B <<< Variable 1

J2 = Shoe <<< Variable 2

J3 = =INDEX($A$1:$F$4,MATCH(J1,A1:A4,0),MATCH(J2,A1:F1,0))
should get you the 9
HTH
 
Stephen,

While
= hat b
would be the type of simple solution that there should be, it does'nt appear
to work.
Is this supposed to work exactly as you've indicated or is there an operator
between hat and b?

This would be much nicer than the Double Lookup that I've been forced to
use.

Tom
 
Highlight your data range, including row and column headings, and click
Insert|Name|Create and check Top row, left column and OK.

Alan Beban
 
It works for me (I know that doesn't help you!).
There is just a space between hat and b.
Go to Tools > Options > Calculation and check that the "Accept labels in
formulas" checkbox is ticked. This allows you to refer to rows and/or
columns of data by their headings, without having to create named ranges.
The space operator takes two ranges as operands and returns the intersection
of them, which in this example would be the cell in column 'hat' and row
'b'.
 
Back
Top