Find the highest value and return adjacent cell until value changes in another cell

**roquinn** · 11-20-2022, 12:32 AM

I am an excel novice, so I hope I am explaining my problem well enough:

Capture.JPG

Field A is the District the candidate is running in, Field B is the candidate name, Field C is the percent of the vote the candidate received, and I need Field D to calculate the District winner's name.

I am trying to calculate the name of the District winner into Field D until the District changes. I know there is probably a much better way to do what I am trying to do, but here is the formula I have pieced together so far: =IF(A2="",A1,A2 (INDEX(B2:B3,0,MATCH(MAX(C2:C3),C2:C3,0))))

Any help is appreciated!

**HansDouwe** · 11-20-2022, 01:07 AM

An option:

Please try

Formula:

Please Login or Register  to view this content.

If the score is tied, multiple winners will be shown.

**MarvinP** · 11-20-2022, 01:15 AM

Hi roquinn and welcome to the forum,

A formula like:

Formula:

Please Login or Register  to view this content.

is what I came up with. See it in the attached and adjust to your sheet.
XLookup from Max Filtered.xlsx

**roquinn** · 11-20-2022, 01:48 AM

That worked perfectly. I do have another question - is there a way to only return the winner in column D? This way I would be able to count how many times the candidate won.

**MarvinP** · 11-20-2022, 02:37 AM

Hi roquin,

Try this extended formula to only show one name for the winner:

Formula:

Please Login or Register  to view this content.

XLookup from Max Filtered One Name.xlsx

**wk9128** · 11-20-2022, 05:28 AM

ans #1

Cell D2 formula , Drag down

Formula:

Please Login or Register  to view this content.

**wk9128** · 11-20-2022, 11:51 AM

@roquinn You're Welcome. Glad to help . Thank You for the feedback and rep.

If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".

**roquinn** · 11-20-2022, 12:53 PM

Hey Marvin,

Thank you for looking into this! So on my spreadsheet, the fields are dist=AH, candidate=AQ, percent=AR going all the way to row 2729. When I change the field values to match the formula you shared down, I get a #N/A error. I even copied the three columns from my spreadsheet into the corresponding columns on the sample spreadsheet you sent and it works for the first 15 cells, then I get a #Calc error. I am not sure what I am doing wrong.

**MarvinP** · 11-20-2022, 01:30 PM

Hi roquinn,

There are a few possible reasons my formula didn't work for you. The first possible reason is the data changed by have a space before or after it. Check to see if the data is exactly the same of what you have and what you are looking for. Second is that you've left off a dollar sign in one of the formulas. Another possible reason is you didn't change the formula to go to your last row.

To figure out what went wrong for you, we'd need a sample of your data in an attached workbook.

**roquinn** · 11-20-2022, 03:48 PM

Here is a sample of my polling data. It has the HouseHistorical and the SenateHistorical data samples.

HouseHistoricPolls.xlsx

Thanks!

**HansDouwe** · 11-20-2022, 06:03 PM

You entered the formula correctly, but The #NA error is caused by cells AH69 through AH73 containing #N/A.
These cells must contain a district.
So yet another cause than one of the possibilities mentioned by Marvin.

**MarvinP** · 11-20-2022, 08:29 PM

Hi roquin,

Thanks to Hans for the explanation.

It looks like you have a #N/A in column L of your sheet which is the original cause of the problem.

Thanks for posting more of your work. As you can imagine, finding the real cause of problems needs lots of detective work.

**wk9128** · 11-20-2022, 08:41 PM

In fact, the problem lies in the column AH , and the problem is solved by changing the original formula a little.

Cell AH2 formula , Drag down

Formula:

Please Login or Register  to view this content.

Cell AS2 formula , Drag down

Formula:

Please Login or Register  to view this content.

**MarvinP** · 11-20-2022, 08:52 PM

Good catch!!

Find the highest value and return adjacent cell until value changes in another cell

LinkBack

Thread Tools

Rate This Thread

Display

Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Re: Find the highest value and return adjacent cell until value changes in another cell

Thread Information

Users Browsing this Thread

Similar Threads

Find value and return adjacent cell value

Vlookup formula on duplicates, then find max value in adjacent cell, return adjacent cell

i want to find a value in a cell and return the value in the adjacent cell

[SOLVED] Formula to find lowest/highest value in a column and return value to that cell

[SOLVED] Find highest value in unsorted column and return data of other cell

[SOLVED] Find cell value and return even number on adjacent cell

Referencing adjacent cell to return highest figure

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions