can I lookup two reference points ??

[colwyn]

Anyone please tell me, can I lookup values by using two reference
points?

Imagine, say, I have two rows of data:

Red 1000 2000 3000 4000 5000
......49.5 67.91 76.3 22.41 31.8

and I want to lokkup "Red" 3000 (to get the result "76.3) -is there a
formula to help me do this?
The s/s is over 320000 rows deep.
Big thanks.
Colwyn.

 

[Alan Moseley]

I think that the answer is to use an array formula, which I have described in
one of my blog entries:-

http://www.amitc.co.uk/blogentry.aspx?ID=13

 

[colwyn]

Thanks Alan. I can see how that works but with over 320000 rows of dat
it would take forever to implement an array formula and I'm trying t
avoid that. I'll wait to see if someone can come up with something tha
avoids an array.
Maybe there is code which would work.
Big thanks.
Colwyn

 

[Dave Peterson]

Debra Dalgleish has some instructions:
http://contextures.com/xlFunctions03.html#IndexMatch2

 

[colwyn]

Dave, that's exactly what I was looking for.
Big thanks.
Colwyn