Unable to get Index/Match to make data from one worksheet to to a summary worksheet

You are trying to use VLOOKUP syntax with an INDEX MATCH and are referencing the destination sheet in the first part of the formula.

Try this:

=INDEX('BT Investors Choice'!$F$17:$F$37,MATCH($F17,'BT Investors Choice'!$I$17:$I$I37),0))

or this:

=INDEX('BT Investors Choice'!$F$17:$F$37,MATCH($F17,'BT Investors Choice'!$J$17:$J$I37),0))

If this doesn't resolve it, please provide a sample workbook (desensitised) - insructions at the top of the page.

Please just ask if you need any further clarification.

Otherwise, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Why not?

On the other forum, go to My Profile and click on the link to Latest Started Threads - find the thread, copy the URL and post it here. Thanks.

I gave you a solution here immediately after you posted here and before you posted on the other forum.

Waiting for you to post the link.

As you appear to be struggling with this (and having opened duplicate threads on both forums about this same issue, which have now been closed|), I will on this occasion provide the required link for you: https://www.excelguru.ca/forums/show...work-correctly

Please take a moment to re-read the forum rules on cross-posting and thread duplication. Thanks.

I have looked at the workbook attached to your duplicate thread, and you are still making the same basic syntax errors with your INDEX MATCH that you were making here, and that I corrected in my first post to this thread.

The formula you need is this:

=INDEX(Combined!C$5:C$9,MATCH($B5,Combined!$B$5:$B$9,0))

However, INDEX MATCH will return the first value it finds for the A/C Number, so you won't get the results I think you are expecting.

It's now time for you to explain in WORDS exactly what you are trying to achieve here, then we can better advise, but if you don't bother implementing the corrections you have already been given, then we won't get very far.

As you have Office 365, the FILTER function might produce what you want:

=FILTER(Combined!C5:D9,Combined!B5:B9=B5:B9)

Maybe this???


=IFERROR(INDEX(Combined!C:C,AGGREGATE(15,6,ROW(Combined!$C$5:$C$20)/((Combined!$A$5:$A$20=A5)*(Combined!$B$5:$B$20=B5)),COUNTIFS(A$5:A5,A5,B$5:B5,B5))),"")

OK. I'm going to take a deep breath and try again. Please do me the courtesy of reading very carefullly what I am telling you this time, because you clearly haven't so far.

=INDEX(Combined!$A$5:$A$9,MATCH($A5,Combined!$A$5:$A$9),3)

The syntax here is incorrect - AGAIN!!!

Look carefully at the differences in the one that works here:

=INDEX(Combined!C$5:C$9,MATCH($B5,Combined!$B$5:$B$9),0)

However, as I told you before, this is not the correct function for the results you want.

This does what you want, I believe:

=FILTER(Combined!$C$5:$D$9,Combined!$B$5:$B$9=$B$5:$B$9)

See attached - sections highlighted in YELLOW.

Using your formula and placing it in the correct colum of the sample worksheet(s) produces the either a Ref# error of or Flow error.

#REF error - syntax was wrong - so it wasn't my formula any longer (see above for correction).
Flow (#SPILL) error because you need to clear everything out of the way first - I didn't mention this, sorry.

And here is a copy that does the whole lot for you using this in cell A12:

=FILTER(Combined!$A$5:$D$14,Combined!$A$5:$A$14="BT Investors Choice")

The bit in red could just as easily be a cell reference on the summary sheet, e.g.

A1="BT Investors Choice" (without the speech marks) and in A18:

=FILTER(Combined!$A$5:$D$14,Combined!$A$5:$A$14=A1)

As has been explained INDEX/MATCH typically (normally) returns only the first matching record, assuming that you have the structure of the function right. You CAN use it to get multiple entries returned but the construct is more complex.

Excel 365 has introduced FILTER and you can use that to extract multiple matching records.

For this specific example, try this:

Formula:

Please Login or Register  to view this content.

As your example is very limited and, I suspect, not very realistic, it may need some adjustment for the live data.

Originally Posted by AliGW

... Please take a moment to re-read the forum rules on cross-posting and thread duplication. Thanks.

Ok. I get it but how do I either stop this or flag the cross posting (as requested)? I DO want to be compliant but the site is not at all clear on how to be so.

I don't think there is much that is ambiguous about our cross-posting rule:

3. Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked.

Please do tell me what you aren't clear on here and I'll explain. Cross-posting is allowed, but you must declare it.

As for duplicate posting, our rules state:

5. Do not open more than ONE thread per issue here. Expect duplicates to be closed.

Does this make sense?

The rules are the same over at ExcelGuru, where I also happen to be a Moderator.