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
|