Intersection Lookup
Posted by Fizzy on March 20, 2001 12:02 PM
Hi
Believe me I have tried but my head is now spinning.
I have a range (say A1:F22) that has labeled rows and labeled cols. Col A is client rating and other cols (total 7) are details of client sizes in various buckets. The actual values at the intersections are pricing values.
How do I lookup in one go the value at the intersection of a given client rating and a client size? Thus the client rating AND the client size are both variable. So for example I want to look up what pricing applies to a client rated AA with a size of $1m. Similarly another example would be a client with a rating of BBB and size of $10m
Vlookup obviously returns the value if there is only ONE variable and, for the life of me, can't see how I would apply INDEX +/or MATCH to obtain the value. Maybe match/index would not help anyway.
I tried using vlookup in a two stage process but got terribly confused (which is to say more about the state of my mind than the flexibility of Excel!).
If anyone has the time to answer I would be most grateful. Project beckons.
many thx
Posted by Loren on March 20, 2001 12:24 PM
The lookup wizard should create this for you.
Posted by cpod on March 20, 2001 12:48 PM
Just use two matchs to feed to the index function:
=INDEX(A1:G22,MATCH(J2,A1:A22),MATCH(I2,A1:G1,0))
where I2 is the size and J2 is the rating
Posted by cpod on March 20, 2001 12:48 PM
Just use two matches to feed to the index function:
=INDEX(A1:G22,MATCH(J2,A1:A22),MATCH(I2,A1:G1,0))
where I2 is the size and J2 is the rating
Posted by Mark W. on March 20, 2001 1:07 PM
Fizzy, this is easier than you might think.
Suppose that your ratings, {"A";"AA";"AAA";"B"},
are in cells A2:A5, and your sizes, {"Small","Medium","Large"},
are in cells B1:D1. Your prices would be entered
into cells B2:D5 -- let's use:
{32,43,35
;39,16,44
;16,44,26
;33,32,37}
Now for the intersection...
The formula, =AAA Large , will return the value, 26.
Posted by Mark W. on March 20, 2001 1:13 PM
BTW...
You'll need to have "Accept labels in formulas" checked.
This option can be found on the Tools Options...
Calculation tab.
Posted by SJC on March 20, 2001 2:44 PM
Mark, this is awesome! If I were to do this lookup on another sheet (i.e., my data is in one sheet and the formula is in another sheet), what would be the syntax?
Posted by Mark W. on March 20, 2001 3:02 PM
Labels are not known globally; therefore, you'd
have to Excel's Insert Name Create... menu
command. Using my sample data you'd select cells
A1:D5, choose the Insert Name Create... menu
command, check both "Top row" and "Left column"
boxes, and press OK. Now, you can use a reference
such as =Book1!AAA Book1!Large . Note that Insert
Name Create... makes names global so it uses the
workbook name not the worksheet name. If you
enter =Sheet1!AAA Sheet!Large and those names
aren't defined on Sheet1 (locally) the formula
will revert to =Book1!AAA Book1!Large .
Posted by Aladin Akyurek on March 20, 2001 3:13 PM
Just one phone line & no ADSL, I've missed the opportunity to take this up... Expected you to know the space operator, of course. Hey, what is the icon for a sad face?
Posted by Mark W. on March 20, 2001 3:34 PM
: (
Hey, here's a new one I just made up...
(:#) Hannibal Lecter
Posted by Mark W. on March 20, 2001 3:35 PM
: (
Looks, better with an added space...
(: #) Hannibal Lecter
Posted by Mark W. on March 20, 2001 4:00 PM
Warning...
Don't use this approach if you're planning to sort
your table rows/columns.
Posted by Fizzy on March 21, 2001 1:34 AM
Incredible! (as soomeone said awesome).
I particularly liked the method advocated by Mark W (so simple, so neat, so intuitive).
Method mentioned by CPOD has plus of not having range name table populated with many entries if lookup on another worksheet.
I found CPOD construction gave some errors but fixed it with following construct:
=INDEX(A1:G22,MATCH(F2,A1:A22,FALSE),MATCH(G2,A1:G1,FALSE))
Many thanks for two solutions when one would have done.
Hmmm - am wondering if a combination of Vlookup and Hlookup may also work?
