Match based on calculated value

[Tekhnikos]

I am trying to use the INDEX and MATCH functions based on a calculated value.

=INDEX(Scoring!G11:H50,MATCH(C3,Scoring!H11:H50,0),1)

Cell C3 is a calculated value:=C2*C6-E6

The result in the cell is #N/A.. is there a way to use a calculated value in
a MATCH function

 

[Bernie Deitrick]

Tekhnikos,

Your values in H11:H50 need to be sorted ascending, or change the 0 in this
part

MATCH(C3,Scoring!H11:H50,0)

to False:

MATCH(C3,Scoring!H11:H50,False)

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

What is the result of =C2*C6-E6 ?

And what kind of values are in H11:H50 ?

What your formula is telling you is that the result of =C2*C6-E6 does not
match any number in H11:H50.

It could be that =C2*C6-E6 returns a decimal value that does not *exactly*
match a number in H11:H50.

 

[Tekhnikos]

Thanks for the suggestion, still didn't work.. any other ideas?

 

[Tekhnikos]

The result of =C2*C6-E6 is a number

The values in column H are numbers

 

[T. Valko]

*If* =C2*C6-E6 returns 10.000002 and you're trying to match it to 10 (or
vice versa) it won't work!

What does =C2*C6-E6 return in C3 and what value in your lookup table is it
supposed to match?

Let's assume the cell B10 in the lookup table is supposed to match the value
in cell C3. What result do you get when you enter this formula:

=C3=B10