Using index and match

[george]

Hi all-
I’m, 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

 

[Jim Thomlinson]

Try this...

=INDEX(Formulas!$K$3:$K$6,MATCH(AM7,Formulas!$J$3:$J$6, false))

 

[Jim Thomlinson]

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.

 

[george]

Hi Jim-
Thanks taking time to help me here. However, I’m 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?

 

[george]

Hi Jim-
Thanks for taking time to help me here. However, I’m 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?

 

[george]

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