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

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

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!

2. Re: Find value based on column/row intersect (Lookup?) (2002)

Hi Sarah,

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

Cheers

3. Re: Find value based on column/row intersect (Lookup?) (2002)

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

4. Re: Find value based on column/row intersect (Lookup?) (2002)

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

5. Re: Find value based on column/row intersect (Lookup?) (2002)

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?

6. Re: Find value based on column/row intersect (Lookup?) (2002)

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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•