| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using index and match
Hi all-
Im, trying to use an index and match function to return text from cells on anther sheet. =INDEX(Formulas!$J$3:$K$6,MATCH(AM7,Formulas!$J$3: $K$6)) The formula looks for a value that matches AM7, it is suppose to return text from cells on another page shown below. I keep getting the dreaded #n/a. I have the text organized in cells Clmn J Clmn K Row 3 4.0 This was out-standing Row 4 2.8 Pretty good but you need ot work on Row 5 1.5 You really need to focus on this more 0.0 No comment here Anyhelp would be very appraciated Cheers George |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using index and match
Try this...
=INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, false)) -- HTH... Jim Thomlinson "george" wrote: Hi all- Im, trying to use an index and match function to return text from cells on anther sheet. =INDEX(Formulas!$J$3:$K$6,MATCH(AM7,Formulas!$J$3: $K$6)) The formula looks for a value that matches AM7, it is suppose to return text from cells on another page shown below. I keep getting the dreaded #n/a. I have the text organized in cells Clmn J Clmn K Row 3 4.0 This was out-standing Row 4 2.8 Pretty good but you need ot work on Row 5 1.5 You really need to focus on this more 0.0 No comment here Anyhelp would be very appraciated Cheers George |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using index and match
That formula that I posted looks for an exact match. if you are not dealing
with exact matches then... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, -1)) I used -1 becuase the order is descending. From the Help on Match If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Try this... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, false)) -- HTH... Jim Thomlinson "george" wrote: Hi all- Im, trying to use an index and match function to return text from cells on anther sheet. =INDEX(Formulas!$J$3:$K$6,MATCH(AM7,Formulas!$J$3: $K$6)) The formula looks for a value that matches AM7, it is suppose to return text from cells on another page shown below. I keep getting the dreaded #n/a. I have the text organized in cells Clmn J Clmn K Row 3 4.0 This was out-standing Row 4 2.8 Pretty good but you need ot work on Row 5 1.5 You really need to focus on this more 0.0 No comment here Anyhelp would be very appraciated Cheers George |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using index and match
Hi Jim-
Thanks taking time to help me here. However, Im still getting the N/A where the text should show up. I wanted the formula to look for the exact match 4.0,2.8,¦ that sort of thing, then return the text corresponding to the value next to it that matches the value in the first cell AM7. So it looks at cell AM7 then goes and finds the text in cells Formulas!$K$3:$K$6,next to the value in AM7. Is this in the best way to do this? "Jim Thomlinson" wrote: That formula that I posted looks for an exact match. if you are not dealing with exact matches then... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, -1)) I used -1 becuase the order is descending. From the Help on Match If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Try this... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, false)) -- HTH... Jim Thomlinson "george" wrote: Hi all- Im, trying to use an index and match function to return text from cells on anther sheet. =INDEX(Formulas!$J$3:$K$6,MATCH(AM7,Formulas!$J$3: $K$6)) The formula looks for a value that matches AM7, it is suppose to return text from cells on another page shown below. I keep getting the dreaded #n/a. I have the text organized in cells Clmn J Clmn K Row 3 4.0 This was out-standing Row 4 2.8 Pretty good but you need ot work on Row 5 1.5 You really need to focus on this more 0.0 No comment here Anyhelp would be very appraciated Cheers George |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using index and match
Hi Jim-
Thanks for taking time to help me here. However, Im still getting the N/A where the text should show up. I wanted the formula to look for the exact match 4.0,2.8,¦ that sort of thing, then return the text corresponding to the value next to it that matches the value in the first cell AM7. So it looks at cell AM7 then goes and finds the text in cells Formulas!$K$3:$K$6,next to the value in AM7. Is this in the best way to do this? "Jim Thomlinson" wrote: That formula that I posted looks for an exact match. if you are not dealing with exact matches then... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, -1)) I used -1 becuase the order is descending. From the Help on Match If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Try this... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, false)) -- HTH... Jim Thomlinson "george" wrote: Hi all- Im, trying to use an index and match function to return text from cells on anther sheet. =INDEX(Formulas!$J$3:$K$6,MATCH(AM7,Formulas!$J$3: $K$6)) The formula looks for a value that matches AM7, it is suppose to return text from cells on another page shown below. I keep getting the dreaded #n/a. I have the text organized in cells Clmn J Clmn K Row 3 4.0 This was out-standing Row 4 2.8 Pretty good but you need ot work on Row 5 1.5 You really need to focus on this more 0.0 No comment here Anyhelp would be very appraciated Cheers George |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using index and match
Hi Jim
Never mind, it works now, I found that one of the reverence cells was nt set to display decimals (it was rounding). Thanks You for your help here ! Cheers George "george" wrote: Hi Jim- Thanks for taking time to help me here. However, Im still getting the N/A where the text should show up. I wanted the formula to look for the exact match 4.0,2.8,¦ that sort of thing, then return the text corresponding to the value next to it that matches the value in the first cell AM7. So it looks at cell AM7 then goes and finds the text in cells Formulas!$K$3:$K$6,next to the value in AM7. Is this in the best way to do this? "Jim Thomlinson" wrote: That formula that I posted looks for an exact match. if you are not dealing with exact matches then... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, -1)) I used -1 becuase the order is descending. From the Help on Match If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Try this... =INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3: $J$6, false)) -- HTH... Jim Thomlinson "george" wrote: Hi all- Im, trying to use an index and match function to return text from cells on anther sheet. =INDEX(Formulas!$J$3:$K$6,MATCH(AM7,Formulas!$J$3: $K$6)) The formula looks for a value that matches AM7, it is suppose to return text from cells on another page shown below. I keep getting the dreaded #n/a. I have the text organized in cells Clmn J Clmn K Row 3 4.0 This was out-standing Row 4 2.8 Pretty good but you need ot work on Row 5 1.5 You really need to focus on this more 0.0 No comment here Anyhelp would be very appraciated Cheers George |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
Linear Mode
