Solved: DOUBLE LOOKUP HELP

[gimli]

Hi all,

Ok...needing some suggestions on how to lookup some data in a large table. Below is a sample of the data.

I need to pull the value from column D when column A is an exact match and column C is within a range.

EX. 1

Lets say when D5 = .064 and E5=.250 I need to pull .005 because .250 is less than or equal too .301.


EX 2

When D5 =.103 and E5 = .140 I need to pull .005 because .140 less than or equal too .143

Column C you have assumed values...

Ex:

When Column A = .064, Column C .239 thru .301 (including .239 & .301) will have a Column D value of .005 even though its not listed. And so on down the list...

Im thinking Index match?? But im kinda stuck here.

thanks much

[gimli]

Looks like my copy paste didnt work well

here is a xls with the data

[mdmackillop]

Your examples don't seem to relate to your data. On the face of it though, you should add headers to your data, set up a Criteria range and use Advance Filter.

[gimli]

Ok..Ill try an re think this

[gimli]

Ok...lets try this again. Trying to explain better.

I attached a spreadsheet which has an example of what im trying to do but not quite getting there. Keep in mind the true set of data is much larger.

When you look at the spread sheet I am searching a table using 2 criteria.
Criteria T will always find an exact match in Column T.
Critera B will not always find an exact match which is causing me my headache.

I am using and index/match. I am using match type 0 which will only work for an exact match. So right now the formula works if the search critera are exact matches.

I am trying to get this to work if Critera B is not an exact match.

Example: See spreadsheet

Critera T=.064
Critera B=.0301

Using the formula in L4... it pulls .005 since both critera are exact matches.

If someone enters:

Critera T=.064
Critera B = Any number >= .239 or <=.301(See header B) I want to pull the value .005 which is common to both.

or

If someone enters:

Critera T=.070
Critera B = Any number >= .070 or <=.489 (see header B) I want to pull the value .005 which is common to all numbers

Not sure if this is possible using a formula or maybe someone can shoot some vba at it.

thanks much

[mdmackillop]

No need for VBA, but I've added it to run the Filter

[gimli]

MD,

The filter works great. What I need to do though is pull the value in column V in this case .005 after runing the filter and use it in a formula.

So someone will be entering data into cell I3 and I4 (my posted spreadsheet) and I want to pull the value into a formula.

If I can get my index/match to work with match type 1 I might be in luck.



Not sure If im explaining this right....

[mdmackillop]

I don't see how entering a value in I4 gives two vales to be compared. Also, can there be more than on result which matches your limits.

[gimli]

MD

See attached for hopefully a better explanation. Thanks for hanging in there. My explanation is probably weak..ha

[mdmackillop]

Try this

=SUMPRODUCT(--(MyH1 = I3),--(SMALL(ABS(I4-MyH3),1)=ABS(I4-MyH3)),MyH4)

BTW is was this line that caused all the confusion.


I need to pull the value from column D when column A is an exact match and column C is within a range.

[gimli]

Ahaaa! Exactly what I was looking for!

thanks very much for the help!

Comunication is always the biggest barrier.

[mdmackillop]

It's not elegant!

[gimli]

Hey MD,

Would you mind checking one more thing? On your spread sheet that you posted with solution,

If you enter the values

H1 - .103
H3 - .615

H4 - Returns 0

Closest value is .612 which should return .009

I notice that when you get to far away from a value in the list it poulates 0. Do you think that something that can be fixed?

[mdmackillop]

=SUMPRODUCT(MyH4,--((ABS(I4-MyH3)/((I3=MyH1)+10^-10))=SMALL(ABS(I4-MyH3)/((I3=MyH1)+10^-10),1)))

[gimli]

Hey thanks..works better...really close.

Still notice that when

H1 = .070
H3 = .520
H4 - .012 - not a good number

or

H1 = .070
H3 = 1.145
H4 - .021 - not a good number

[mdmackillop]

=SUMPRODUCT(MyH4,--((ABS(I4-MyH3)/((I3=MyH1)+10^-10))=SMALL(ABS(I4-MyH3)/((I3=MyH1)+10^-10),1)))/SUMPRODUCT(--((ABS(I4-MyH3)/((I3=MyH1)+10^-10))=SMALL(ABS(I4-MyH3)/((I3=MyH1)+10^-10),1)))

Getting there! Enter as an array formula

[gimli]

Hey..thanks much

Looks like its 99%.

Looks like when H4 changes value in the table..in this example when H4 increments from .024 to .027 it adds a small value at half way mark if that makes sence. Not sure if its fixable...maybe at halfway point always pull next value will work.



H1 = .070
H3 = 3.613
H4 - .024 - good number

H1 = .070
H3 = 3.614
H4 - .026 - adds .002 to .024 -

H1 = .070
H3 = 3.615
H4 = .027 - good number

[mdmackillop]

Some of your data return two results; possibly more. The formula averages the results.

[gimli]

Ok..thanks for the help :)