Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Exact Match using INDEX, MATCH
Excel 2007.
I have two worksheets named "Open 1st Go" & "Open 2nd Go". The first worksheet contains Column A (Rider), Column B (Horse), Column C (1st Go Time), Column D (2nd Go Time), and Column E (Average Time). The second worksheet contains Column A (Rider), Column B (Horse), and Column C (2nd Go Time). I need to match the rider/horse from the 1st go sheet exactly to the rider/horse on the 2nd go worksheet (as some riders ride more than one horse and therefore would have a different time) and bring back their 2nd go time into Column D. This is the formula that I used, but it only matches the rider, not the combination of rider/horse, and brings back only the one time not distinguishing between different horse names. =INDEX('OPEN 2ND GO'!$A$1:$C$34, MATCH(A2,'OPEN 2ND GO'!$A$1:$A$34,), MATCH("TIME",'OPEN 2ND GO'!$A$1:$C$1,)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Exact Match using INDEX, MATCH
DoubleUU wrote:
Excel 2007. I have two worksheets named "Open 1st Go" & "Open 2nd Go". The first worksheet contains Column A (Rider), Column B (Horse), Column C (1st Go Time), Column D (2nd Go Time), and Column E (Average Time). The second worksheet contains Column A (Rider), Column B (Horse), and Column C (2nd Go Time). I need to match the rider/horse from the 1st go sheet exactly to the rider/horse on the 2nd go worksheet (as some riders ride more than one horse and therefore would have a different time) and bring back their 2nd go time into Column D. This is the formula that I used, but it only matches the rider, not the combination of rider/horse, and brings back only the one time not distinguishing between different horse names. =INDEX('OPEN 2ND GO'!$A$1:$C$34, MATCH(A2,'OPEN 2ND GO'!$A$1:$A$34,), MATCH("TIME",'OPEN 2ND GO'!$A$1:$C$1,)) Here's one way. This is an array formula (complete by pressing Ctrl + Shift + Enter): =INDEX('Open 2nd Go'!$A$1:$C$34,MATCH(1,('Open 1st Go'!A2='Open 2nd Go'!$A$1:$A$34)*('Open 1st Go'!B2='Open 2nd Go'!$B$1:$B$34),0),3) Here's another way. It's not an array formula, and I find a little easier to follow. This assumes there are no duplicate horse+rider combinations on 'Open 2nd Go': =SUMPRODUCT(--('Open 1st Go'!A5='Open 2nd Go'!$A$1:$A$34),--('Open 1st Go'!B5='Open 2nd Go'!$B$1:$B$34),('Open 2nd Go'!$C$1:$C$34)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Exact Match using INDEX, MATCH
smartin wrote:
DoubleUU wrote: Excel 2007. I have two worksheets named "Open 1st Go" & "Open 2nd Go". The first worksheet contains Column A (Rider), Column B (Horse), Column C (1st Go Time), Column D (2nd Go Time), and Column E (Average Time). The second worksheet contains Column A (Rider), Column B (Horse), and Column C (2nd Go Time). I need to match the rider/horse from the 1st go sheet exactly to the rider/horse on the 2nd go worksheet (as some riders ride more than one horse and therefore would have a different time) and bring back their 2nd go time into Column D. This is the formula that I used, but it only matches the rider, not the combination of rider/horse, and brings back only the one time not distinguishing between different horse names. =INDEX('OPEN 2ND GO'!$A$1:$C$34, MATCH(A2,'OPEN 2ND GO'!$A$1:$A$34,), MATCH("TIME",'OPEN 2ND GO'!$A$1:$C$1,)) Here's one way. This is an array formula (complete by pressing Ctrl + Shift + Enter): =INDEX('Open 2nd Go'!$A$1:$C$34,MATCH(1,('Open 1st Go'!A2='Open 2nd Go'!$A$1:$A$34)*('Open 1st Go'!B2='Open 2nd Go'!$B$1:$B$34),0),3) Here's another way. It's not an array formula, and I find a little easier to follow. This assumes there are no duplicate horse+rider combinations on 'Open 2nd Go': Oops, that second formula should have been as follows for cell D2: =SUMPRODUCT(--('Open 1st Go'!A2='Open 2nd Go'!$A$1:$A$34),--('Open 1st Go'!B2='Open 2nd Go'!$B$1:$B$34),('Open 2nd Go'!$C$1:$C$34)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Exact Match using INDEX, MATCH
You are a life saver! Thank you so much for taking the time to help me - I
struggled for weeks on this formula! "smartin" wrote: smartin wrote: DoubleUU wrote: Excel 2007. I have two worksheets named "Open 1st Go" & "Open 2nd Go". The first worksheet contains Column A (Rider), Column B (Horse), Column C (1st Go Time), Column D (2nd Go Time), and Column E (Average Time). The second worksheet contains Column A (Rider), Column B (Horse), and Column C (2nd Go Time). I need to match the rider/horse from the 1st go sheet exactly to the rider/horse on the 2nd go worksheet (as some riders ride more than one horse and therefore would have a different time) and bring back their 2nd go time into Column D. This is the formula that I used, but it only matches the rider, not the combination of rider/horse, and brings back only the one time not distinguishing between different horse names. =INDEX('OPEN 2ND GO'!$A$1:$C$34, MATCH(A2,'OPEN 2ND GO'!$A$1:$A$34,), MATCH("TIME",'OPEN 2ND GO'!$A$1:$C$1,)) Here's one way. This is an array formula (complete by pressing Ctrl + Shift + Enter): =INDEX('Open 2nd Go'!$A$1:$C$34,MATCH(1,('Open 1st Go'!A2='Open 2nd Go'!$A$1:$A$34)*('Open 1st Go'!B2='Open 2nd Go'!$B$1:$B$34),0),3) Here's another way. It's not an array formula, and I find a little easier to follow. This assumes there are no duplicate horse+rider combinations on 'Open 2nd Go': Oops, that second formula should have been as follows for cell D2: =SUMPRODUCT(--('Open 1st Go'!A2='Open 2nd Go'!$A$1:$A$34),--('Open 1st Go'!B2='Open 2nd Go'!$B$1:$B$34),('Open 2nd Go'!$C$1:$C$34)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DSUM Criteria - find exact match only | Excel Worksheet Functions | |||
Find exact match, same workbook, different sheets | Excel Discussion (Misc queries) | |||
Find a not exact match using vlookup | Excel Discussion (Misc queries) | |||
using vlookup to find exact match | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions |