NEED HELP: How do I index only among rows that contain a specific text

[Vincodi]

I am trying to use a formula to find the largest value and return the name in an adjacent column, but I only want it to search among the rows that have the same label.

Here is the formula I am using =INDEX('DATA TAB'!B$9:B$1000,(MATCH(LARGE('DATA TAB'!D$9:D$1000,1),'DATA TAB'!D$9:D$1000,0)))

This works, but it's looking in all the rows. I want to add an element to this formula that tells excel "Among the rows that have the word "Buick", use the formula. I can't figure out how to limit the formula to search for all the rows that have the word "Buick" in column A before doing the above Index. Please help!!!

[MarvinP]

Hi Vincodi and welcome to the forum,

I'm trying to understand your question and build a worksheet that might be close. See my answer using a Pivot Table and the Rank feature in Pivots. I might not even be close to what you want with the attached.

Where to buy a car.xlsx

You need to give us a sample workbook so we can understand the problem better and show how you want the answer.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Vincodi]

See the attachment. My goal is to paste the data into the DATA Tab and have the Boxes in the Buick populate with the only the buick dealers. My current formula cannot differentiate the Buick dealers form the rest.

[63falcondude]

In B6, try this:

=INDEX('DATA TAB'!B$2:B$511,(MATCH(LARGE(IF('DATA TAB'!A$2:A$511="BUICK",'DATA TAB'!D$2:D$511),1),'DATA TAB'!D$2:D$511,0))) Ctrl Shift Enter

[63falcondude]

Note that you can also substitute ROWS(A$1:A1) for 1 so that you do not have to manually adjust each formula in column B.

That is:

=INDEX('DATA TAB'!B$2:B$511,(MATCH(LARGE(IF('DATA TAB'!A$2:A$511="BUICK",'DATA TAB'!D$2:D$511),ROWS(A$1:A1)),'DATA TAB'!D$2:D$511,0))) Ctrl Shift Enter

[Vincodi]

Great that works!

[JohnTopley]

Try

=INDEX('DATA TAB'!B$9:B$1000,(MATCH(LARGE(('DATA TAB'!D$9:D$1000)*('DATA TAB'!A$9:A$1000=S1),1),('DATA TAB'!D$9:D$1000)*('DATA TAB'!A$9:A$1000=S1),0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[63falcondude]

Great, glad we could help.

If that solved your question, please mark this thread as SOLVED.

[Vincodi]

Actuallly, I found an error. The formula is not using the BUICK qualifier to ignore the rows that don;t have BUICk. For example, there are several dealers that have the same number of sales, and the formula is not skipping those without BUICK. Any suggestions?

[63falcondude]

All of the formulas that have been provided in this thread are working for me.

Exactly where do you see an error?

[JohnTopley]

Try formula in post #7 but change start row to 3 i.e.

=INDEX('DATA TAB'!B$3:B$1000,(MATCH(LARGE(('DATA TAB'!D$3:D$1000)*('DATA TAB'!A$3:A$1000=S1),1),('DATA TAB'!D$3:D$1000)*('DATA TAB'!A$3:A$1000=S1),0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Vincodi]

The formula is working, but is returning values from rows that do not meet the "BUICK" criteria. It's working for the first 3 dealers, but there are 5 dealers with 1 sales. The formula is returning dealers with 1 sles which do not meet the BUICK criteria.

[JohnTopley]

See attached:

[63falcondude]

Correction to my formula:

=INDEX('DATA TAB'!B$2:B$511,(MATCH("BUICK"&LARGE(IF('DATA TAB'!A$2:A$511="BUICK",'DATA TAB'!D$2:D$511),ROWS(A$1:A1)),'DATA TAB'!A$2:A$511&'DATA TAB'!D$2:D$511,0))) Ctrl Shift Enter

[Vincodi]

That worked...sort of. It ignored the non-Buick rows and output the dealer with 1 sales, but there are 4 more dealers in the buick rows with 1 sales and it's not outputting those. It just outputs the first 1 sales dealer 5 times. Any idea how to get it to out put the other 4 delaers with 1 sales?

[63falcondude]

Vincodi, you have multiple people contributing to this thread. Please include who you are referring to.

[Vincodi]

Sorry. I was referring to you, 63FalconDude.

[63falcondude]

I see. There can be multiple instances of BUICK in column A and 14 (for example) in column D.

INDEX MATCH pulls the first match. This will require the formula to pull multiple matches.

John, I believe that your solution will have the same issue.

I'll have to think on this one. I know that I can make it work with a helper column but I'd rather not do that if I can help it.

[MarvinP]

I'm so happy you guys are using my example data. But if there are duplicates the Sum in my Pivot Table doesn't work. If we use Min instead of Sum it might give what is wanted. See attached. Pivot Tables don't need formulas.

Where to buy a car 2.xlsx

[Vincodi]

Vincodi, you have multiple people contributing to this thread. Please include who you are referring to.

Sorry. I was referring to you last formula. Totally worked except for when the number of sales are equal. It will only output the first dealer. So if 5 dealers have 1 sales, it will output the first dealer 5 times. Anyway to have it output each of the 5 dealers with 1 sale?

[JohnTopley]

Try

=INDEX('DATA TAB'!B$3:B$1000,(MATCH(LARGE(('DATA TAB'!D$3:D$1000)*('DATA TAB'!A$3:A$1000=$S$1)+(ROW(D$3:D$1000)*10^-3),ROWS($1:14)),('DATA TAB'!D$3:D$1000)*('DATA TAB'!A$3:A$1000=$S$1)+(ROW(D$3:D$1000)*10^-3),0)))

[Vincodi]

I see. There can be multiple instances of BUICK in column A and 14 (for example) in column D.

INDEX MATCH pulls the first match. This will require the formula to pull multiple matches.

John, I believe that your solution will have the same issue.

I'll have to think on this one. I know that I can make it work with a helper column but I'd rather not do that if I can help it.

Is there something we can add to the current formula =INDEX('DATA TAB'!$B$10:$B$1000,(MATCH("BUICK"&LARGE(IF('DATA TAB'!$A$10:$A$1000="BUICK",'DATA TAB'!J$10:J$1000),ROWS($A$1:$A3)),'DATA TAB'!$A$10:$A$1000&'DATA TAB'!J$10:J$1000,0))) which will tell it if the dealer output in the row above has the same number of sales, exclude that and output the next dealer?

[Vincodi]

I'm so happy you guys are using my example data. But if there are duplicates the Sum in my Pivot Table doesn't work. If we use Min instead of Sum it might give what is wanted. See attached. Pivot Tables don't need formulas.

Attachment 542979

Thank for your help. For this project, my goal is to paste raw data and have the formulas do all the work. I am trying to automate a very labor intensive process for employees who are very excel illiterate, so my hope is to use only formulas as pivot tables would cause too much confusion. Thanks for your help with this.

[MarvinP]

so my hope is to use only formulas as pivot tables would cause too much confusion.

Keep typing those formulas then!

[63falcondude]

Due to the complexity of this, if you insist on using formulas, I felt it best to create a helper column.

In the DATA TAB worksheet:

H3 =IFERROR(LARGE(IF(A$2:A$511="BUICK",D$2:D$511),ROWS(A$1:A1)),"") Ctrl Shift Enter
Drag this down until you get blanks (or to row 511).

I3 =IFERROR(INDEX(B$3:B$510,SMALL(IF((D$3:D$510=H3)*(A$3:A$510="BUICK"),ROW(D$3:D$510)-(ROW(D$3)-1)),COUNTIF(H$3:H3,H3))),"") Ctrl Shift Enter
Drag this down as far as the formula in column H.

You can put these formulas wherever you want and hide the helper column if need be. Just note that you will have to include the sheet references if moving the formulas off of the DATA TAB worksheet.

See attachment for clarification.