lookup

E

Eric

I am looking for either a macro or a function that will return a certain value.
ie: % With in Limits
test 100 99 98 97 96 .......
1 0.9 0.8 0.6 0.3 0.1
2 1.8 1.7 1.5 1.3 1.0
3 2.9 2.8 2.4 2.2 2.1

Test #2 find number 1.5 Return value 98

I hope someone can help me.
Eric
 
R

Ron Coderre

With your posted data list in A1:F4

For an exact match...

Try this:
A8: (Test to use....eg 2)
A9: (value to find...eg 1.5)

This formula returns the top row score
associated with the A9 score
for the A8 test.
=INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4,0),),0))

NOTE: if the A9 value is not found....the formula returns an error.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
E

Eric

it does help but if it isn't an exact match I still need it to return a
value. between the two numbers. ie: if it's between 98 and 99 I need it to
mathmatically calculate if it would be 98.something......

Eric
 
R

Ron Coderre

OK...I understand.....
You want to search a particular test for a certain value.
If that exact value is found, then return the associated top row value.
If that exact value is not found, then you want to interpolate the top
row values.

Again...using your posted data list in A1:F4

and
A8: (Test to use....eg 2)
A9: (value to find...eg 1.5)

Try this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just
ENTER:
A10: =INDEX(A1:F1,MATCH(A9,OFFSET(A1:F1,MATCH(A8,A2:A4,0),),-1))-
(INDEX(OFFSET(B1:F1,MATCH(A8,A2:A4,0),),MATCH(A9,OFFSET(B1:F1,
MATCH(A8,A2:A4,0),),-1))-A9)/SUM(OFFSET(INDEX(B3:F3,MATCH(A9,
OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)),,,1,2)*{1,-1})

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
E

Eric

Ron,

This formula isn't working......I Can't get it to work using Alt+Shift+Enter
or by just using enter. Can you help? Please
 
R

Ron Coderre

That should be.......CTRL+Shift+Enter
Not ALT+Shift+Enter

Does that make it work?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
E

Eric

Hey Ron,

I was wondering if you had any other solutions to the problem since the
formula isn't working? Please let me know what you think might be the
problem. Every time I run the formula I get a Div/0 or a num error.
Any help would be appreciated. Thank you

ERic
 
R

Ron Coderre

Eric

I revisited the formula I posted and
just thunked myself on the head!

THIS much shorter, regular formula seems to do what you indicated:

A10: =FORECAST(A9,OFFSET(B1:F1,,MATCH(A9,OFFSET(B1:F1,
MATCH(A8,A2:A4,0),),-1)-1,1,2),OFFSET(B1:F1,MATCH(A8,A2:A4,0),
MATCH(A9,OFFSET(B1:F1,MATCH(A8,A2:A4,0),),-1)-1,1,2))

Also, I updated the file I posted to reflect that change:
http://www.savefile.com/files/1264101

I hope that helps.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
R

Ron Coderre

Well....this is the latest in a series of final solutions :\

Lori (of these forums) used another approach in a
recent post that I like much better.

Adapted for your situation (and implemented in the
file I made available), the formula is:
=PERCENTILE(B1:F1,PERCENTRANK(OFFSET(B1:F1,MATCH(A8,A2:A4,0),,,),A9,20))

http://www.savefile.com/files/1264101

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top