index and match function help required

**jinx31** · 10-24-2016, 06:59 AM

hi there

i am having trouble with a formula using the index and match function. the table i am using has a lot of duplicate numbers which i require. The problem i have with the formula is it searches the array and keeps returning the first value it finds. is there a way to make it find the first value and then ignore it and move onto the next duplicate value and return the value i require.
attached is a small sample(which will probably explain it better than i have) and i have highlighted the areas i have the problems with.
Is there a way to do this without using VBA as i have zero knowledge of it.

many thanks

**Tony Valko** · 10-24-2016, 07:00 AM

No attachment

**Tony Valko** · 10-24-2016, 07:02 AM

To attach a file to your post...

Click the Reply button to open the Reply Editor
Click the Go Advanced button
Scroll down until you see the Manage Attachments button and click that
Click the Browse button and select your file
Click the Open button
Click the Upload button
Click the Close Window button
Click the Submit Reply button

**jinx31** · 10-24-2016, 07:33 AM

hopefully file is attached

**Tony Valko** · 10-24-2016, 07:43 AM

This seems kind of odd.

I'm assuming your real file is setup differently.

This array formula** entered in D4 and copied down:

=INDEX(B:B,SMALL(IF(A$4:A$18=A4,ROW(A$4:A$18)),COUNTIF(A$4:A4,A4)))

This array formula** entered in

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**jinx31** · 10-24-2016, 03:31 PM

thanks guys for the help. I am just a beginner at excel functions and formulas and i am fast finding how powerful they can be used in the right context. I like both options supplied as the same result is achieved. I liked the sample file by chullan as I had tried the count function in a helper column but just wasn't sure how it would help me. Using the indirect function looks interesting as well. The above formula is an all in one function and doesn't require helper columns which is handy to know if I cant include a helper column in my worksheet. I will now look at both of these and break it down and understand how these work for future use.
I liked to thank both of you for the quick response and help. I'm sure this wont be the last time but for now I think we can called this SOLVED.

**Tony Valko** · 10-24-2016, 04:19 PM

Good deal. Thanks for the feedback!

**chullan88** · 10-24-2016, 11:17 PM

Originally Posted by jinx31

thanks guys for the help. I am just a beginner at excel functions and formulas and i am fast finding how powerful they can be used in the right context. I like both options supplied as the same result is achieved. I liked the sample file by chullan as I had tried the count function in a helper column but just wasn't sure how it would help me. Using the indirect function looks interesting as well. The above formula is an all in one function and doesn't require helper columns which is handy to know if I cant include a helper column in my worksheet. I will now look at both of these and break it down and understand how these work for future use.
I liked to thank both of you for the quick response and help. I'm sure this wont be the last time but for now I think we can called this SOLVED.

Hi jinx31,

Thanks for the feedback.

If your problem is solved,Please "add Rep" for all the members who helped you reach the solution.

**chullan88** · 10-24-2016, 07:40 AM

Originally Posted by jinx31

hi there

i am having trouble with a formula using the index and match function. the table i am using has a lot of duplicate numbers which i require. The problem i have with the formula is it searches the array and keeps returning the first value it finds. is there a way to make it find the first value and then ignore it and move onto the next duplicate value and return the value i require.
attached is a small sample(which will probably explain it better than i have) and i have highlighted the areas i have the problems with.
Is there a way to do this without using VBA as i have zero knowledge of it.

many thanks

Hi,

Find attached.
Done using 2 helper columns.

index and match function help required

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

index and match function help required

Re: index and match function help required

Re: index and match function help required

Re: index and match function help required

Re: index and match function help required

Re: index and match function help required

Re: index and match function help required

Re: index and match function help required

Re: index and match function help required

Thread Information

Users Browsing this Thread

Similar Threads

Help with index and / or match formula with multiple match required to return a value

Required Index Match with If function

[SOLVED] Index & Match help required

[SOLVED] Formula required (Index, Match)

[SOLVED] Help required with INDEX and multiple MATCH function - Excel 2007

Index Match only 4 results required

Excel 2007 : INDEX/MATCH help required

Bookmarks

Bookmarks

Posting Permissions