•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Returning the second result from MATCH
Our take
Are you struggling with Excel's MATCH function only returning the first result in the case of ties? You're not alone—many hobbyists tracking win rates share this frustration. Fortunately, there's a way to return the second result (and beyond) without the headache. By leveraging a few clever techniques, you can create a dynamic rankings table that accurately reflects your data and avoids duplicate values. Dive in to discover a solution tailored for Excel 2019 that will transform your tracking efforts and enhance your spreadsheet experience.
I've recently made a hobby of tracking winrates in shows I watch. I'd like to add a rankings table at the end of my spreadsheet, but as seems to have plagued many people, the MATCH function only ever returns the first match in the case of a tie.
The gold-colored table functions using the retrieved cell addresses in the boxes on the right. As long as those cell addresses are correct, the table will function. I would like to tie-proof #2 and on so that they don't return the same value twice. I've been at this for a couple days now, but all the other solutions I've found have been extremely specific to the spreadsheet that the OP is using.
I'm working in Excel 2019. Link to the spreadsheet here.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- I'm making a bracket for my badmiton team that self populates with the winner of each previous round. I'm having trouble with it populating the next bracket before all of the scores are filled out.My computer is kinda butt so I can't take a screenshot rn sry. But basically one match looks like | nameA | score1a | score2a | score3a | | nameB | score1b | score2b | score3b | Rn I have this formula, where it basically checks who won 2 of the 3 previous matches. =if((score1a>score1b)+(score2a>score2b)+(score3a>score3b)>2, nameA, nameB) Now here's my issue: I want the cell to stay blank until all the scores are put in. rn the cell will show nameB so i was like ok lemme use some =isblank or =countblank but the problem I'm running into is it's possible to win the match in 2 sets or in 3 sets. if i say =isblank(score3a, score3b) then anyone who wins in two sets will not be populated. if i say =isblank(score 2a, score2b) once i put in the second set score then it'll show a winner and now my brain hurts. basically im dumb, plz help. also trying to do it with smallest formula possible bc it sucks to have to change a bunch of references for each match. submitted by /u/Downtown-Bet1609 [link] [comments]
- Function to match entries between two tablesTo help me manage my guild members’ scores for a recurring event in my game, I’m looking for a way to ensure that the data from my “DV Score” table is transferred to the “DV Final” table. The first screenshot shows the “DV Score” table and the second screenshot shows the “DV Final” table. Given that the position of members in the ‘DV Final’ table may change, my aim is to ensure that the data from the ‘DV Score’ table is transferred to the other table based on the linked member’s name. I have already tested using =INDEX(E1:E35, MATCH(‘Yurilowel’, “DV Score”!A3:A22, 0)), trying to get it to return the value 15480 from cell C3, but it simply returns an empty cell (EQUIV is the french function for MATCH) https://preview.redd.it/mm8bgpvma7vg1.png?width=619&format=png&auto=webp&s=409fb848088399ef0c24bc86afce5a60e6c56efa https://preview.redd.it/gnmbcn5oa7vg1.png?width=1203&format=png&auto=webp&s=2de4c08f7209107ee869a3eda94ac0cb1af6c837 submitted by /u/Ikatsune [link] [comments]
- Excel dynamic array challenge: sequentially filtering rows by chaining winners to their next matchExcel version: 365 For quite some time now I've been working on a silly project on my spare time. It's not something serious, and my issue is not even critical to what I'm trying to do. It is basically a compilation of (almost) all international football (soccer) matches. You can download the full workbook here to play around with it. Now here's my situation: Consider that I have a range comprised of 6 columns and several rows, named AllTimeMatches Column 1 is the date of a football match. Column 2 is the home team. Column 3 is the home team score. Column 4 is just a separator. Column 5 is the away team score. Column 6 is the away team. I've already used LET(r,AllTimeLeagueMatches,FILTER(r,INDEX(r,,3)<>INDEX(r,,5))) to do away with matches that ended in a draw, because for this part of the project I'm only looking at matches with a win/loss result. What I want now is to look on a row by row basis for the next match by the winner of the previous match. Example: I'm intentionally omitting the column 1 values because they are irrelevant for what I need. Let's assume the range has these rows: Northern Ireland 7 x 2 Wales England 6 x 1 Northern Ireland Wales 3 x 4 Scotland Scotland 2 x 1 Northern Ireland England 2 x 1 Scotland Canada 1 x 4 United States Canada 1 x 2 United States Canada 1 x 2 United States Scotland 5 x 1 Canada Northern Ireland 0 x 2 England In this example, because Northern Ireland won the first match, you need to look for the next match played by Northern Ireland. So, Northern Ireland plays against England in row 2 and loses. Because England won this one, you'll look for the next match played by England. England's next match is in row 5, so rows 3 and 4 will not be shown. England wins again in row 5, so you continue looking for England. Every time the winner changes, you will look for the next match played by the winner until the last row of the range, or until there are no more matches played by the current winner, whichever comes first. In this example, the result would be: Northern Ireland 7 x 2 Wales England 6 x 1 Northern Ireland England 2 x 1 Scotland Northern Ireland 0 x 2 England Is it doable? I've already asked AI. It says it is doable, but none of their proposed formulas work, and I can't figure it out with my current knowledge. Again, no need to burn your neurons with this, but if something figures this out, I'd love it. If my query is not clear, just ask, I'll be more than happy to clarify. submitted by /u/VirtualS1nn3r [link] [comments]
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]