MrExcel.com - Your One Stop for Excel Tips & Solutions

index match return #N/A value..please help me

Posted by kid on March 13, 2000 11:18 PM

is there any macros that can find two marks of people

i mean 2 people with the same marks..e.g

ally 200

bobby 200

i'm using index match and i try to find
people that stands the pole position to below
the problem is when there are 2 or more people
get the same marks.it will turn the #N/A value..

please help me.

Posted by Celia on March 14, 2000 12:23 AM

Kid
I see that this is the fourth time you have asked for help on this without receiving any response.
Perhaps Index/Match is not the best approach. If you would like to E-Mail to me your file (or a specimen sample of it), I will have a look to see if something can be worked out.
Celia


Posted by kid on March 14, 2000 2:47 AM

thanks for your concern Celia..i appreciate it very much ..i will mail my works to you pretty soon and hope you can help me.thank you again


Posted by Royden Proefrock on March 14, 2000 5:25 PM

See my inquiry entry of 24 Feb on this message
board and see if my reply to Celia with my final
solution is what you are looking for.

Posted by Celia on March 14, 2000 7:25 PM


Royden

Although ISNA avoids #N/A appearing, this is not Kid’s main problem.

The problem is that he is using index/match but his look-up table sometimes has more than one line with the same look-up value. When this occurs, only data for the first such value is returned but he wants the data for all such lines.
(I hope my explanation can be followed!).

I have taken the easy way out and sent him a macro to do what he wants. It would be much more elegant to be able to work out a formula.
Does anyone have any ideas?

Celia

Posted by kid on March 15, 2000 2:20 AM

thanks mr royden but i really feel now this is an impossible task..anyway..thanks 4 response

Posted by Jaime on March 15, 2000 9:04 AM

HI GUYS, I AM NOT SURE IF I UNDERSTAND KIDS PROBLEM , BUT IF IT IS A POLE POSITION TYPE PROBLEM I THINK THIS FORMULA MIGHT HELP.
IT RETURNS ANN ARRAY WITH ALL OF THOSE PEOPLE THAT HAVE THE SAME POLE POSITION WHICH IS NEAREST TO THE POLE.
THE DRAWBACK ARE:
THE NAMES MUST BE IN COL A WITH NO LABEL IN THE FIRST ROW .AND THERE CAN'T BE ANY EMPTY ROWS I JUST DONT UNDERSTAND THE ADDRESS FUNCTION.
THE SCORES MUST BE IN COL B

=OFFSET(INDIRECT(ADDRESS( 1+ MATCH(D2,B:B) -COUNTIF(B:B,INDEX($B$1:$B$5,MATCH(D2,B:B))),1)),,,COUNTIF(B:B,INDEX(B:B,MATCH(D2,B:B))),1)

BYE