Find value based on column/row intersect (Lookup?) (2002)

Topic

New Reply

I have a table of values and would like to report on the intersection of the column and row. Please see attached workbook. For example, if Mary’s score is 23 (2 is the column label, 3 is the row label) her value is 150. And if Joe’s score is 54, his value is 265. I read the first digit as the column and the second digit as the row. What kind of formula will I need? Some type of lookup? Or a nested if?? I know the wizards on this board will have a speedy reply. Thanks in advance!

Reply | Quote

Replies

Hi Sarah,

Try: =OFFSET($A$1,LEFT(N2,1),RIGHT(N2,1))
in cell O2, and copy down as far as needed.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks Macropod! That works. Now I’ll do a little research to figure out how it works. Pretty cool!

Reply | Quote

=INDEX(B2:J9,MATCH(–LEFT(N2,1),A2:A9,0),MATCH(–RIGHT(N2,1),B1:J1,0))

Reply | Quote

And thank you, maxflia10. Do either of the formulas perform more efficiently than the other? Or is it simply another way to accomplish the goal?

Reply | Quote

I don’t know if one is more efficient than the other. OFFSET is a volatile function, whereas Index/Match is not. What you can do to eliminate a couple of function calls is to enter the column/row numbers in separate cells. If the matrix is not huge, there shouldn’t be a noticeable difference one way or another. I’d go for the shorter OFFSET, since I’m lazy.

Reply | Quote