Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match based on calculated value
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 -- Tekhnikos |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match based on calculated value
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 "Tekhnikos" wrote in message ... 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 -- Tekhnikos |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match based on calculated value
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. -- Biff Microsoft Excel MVP "Tekhnikos" wrote in message ... 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 -- Tekhnikos |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match based on calculated value
Thanks for the suggestion, still didn't work.. any other ideas?
-- Tekhnikos "Bernie Deitrick" wrote: 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 "Tekhnikos" wrote in message ... 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 -- Tekhnikos |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match based on calculated value
The result of =C2*C6-E6 is a number
The values in column H are numbers -- Tekhnikos "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Tekhnikos" wrote in message ... 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 -- Tekhnikos |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match based on calculated value
*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 -- Biff Microsoft Excel MVP "Tekhnikos" wrote in message ... The result of =C2*C6-E6 is a number The values in column H are numbers -- Tekhnikos "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Tekhnikos" wrote in message ... 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 -- Tekhnikos |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to create a calculated item based on a calculated field | Excel Discussion (Misc queries) | |||
The amount calculated based on two entry criteria (somproduct?) | Excel Discussion (Misc queries) | |||
Calculated Field in Pivot Table Based on Two Counted Fields | Excel Discussion (Misc queries) | |||
Are financial functions calculated based on compound interest? | Excel Worksheet Functions | |||
Pivot table help:calculated field based on previous consecutive va | Excel Discussion (Misc queries) |