On Excel - how do I retrieve value on the same row from most recent date and matching column value? - Stack Overflow

admin管理员组

文章数量:1318572

In a nut shell - I'm creating a workbook which has multiple spreadsheets including spreadsheet of list of all items considered as electrical equipment within the building - which is attached as seen next:

EQUIPMENT REGISTER SPREADSHEET:

This is where I enter all the electrical items that falls under a specific class, inspection or both.

RISK ASSESSMENT SPREADSHEET:

This is where I log all the risk assessments taken on the electrical items as seen on the equipment register spreadsheet.

WHAT'S IN PLACE:
On the Equipment Register - Under Column H / Next Risk Assessment Date - this is using a combination of index, match and max to find the equipment on the risk assessment spreadsheet - matching the Appliance ID but looking for the latest date and then I add the number of risk assessment days to that value from settings - this is done with the following:

=IF(
    AND(A4 <> "", G4 = "YES"),
    IF(
        INDEX('Risk Assessment'!$C$4:$AL$1048576, MATCH(A4, 'Risk Assessment'!$A$4:$A$1048576, 0), 1),
        MAX('Risk Assessment'!$A$4:$A$1048576 = A4, 'Risk Assessment'!$C$4:$C$1048576),
        "RISK NOT RECORDED"
    ),
    "NOT IN USE"
)

MY ISSUE:
From the Equipment Register Spreadsheet - Under the 'Result' column beside next risk assessment date - I want to retrieve the value of column 'Result' from Risk Assessment Spreadsheet but it needs to match both the appliance ID BUT it needs to be the most recent date to show the current Result.

WHAT I'VE TRIED:
Tried so many ways as this is one of the areas I'm trying to work on and spent so many hours looking at different ways and to no luck - I've electrical items to test but need to get these spreadsheets working and then I have a system in place not for myself but for compliance purposes too. My finance officer has suggest Vlookup but failing to see how this works as it needs to be recent date based on matching appliance ID.

BOTTOM LINE:
I do appreciate all the help but need to get this moving on - I used to program a lot back in my early days so for me to start doing this again at work - it's gone out of my mind but don't want to be spending too much time in one area otherwise the cost of spending more and more time on it becomes greater. Thank you for being amazing and awesome!

In a nut shell - I'm creating a workbook which has multiple spreadsheets including spreadsheet of list of all items considered as electrical equipment within the building - which is attached as seen next:

EQUIPMENT REGISTER SPREADSHEET:

This is where I enter all the electrical items that falls under a specific class, inspection or both.

RISK ASSESSMENT SPREADSHEET:

This is where I log all the risk assessments taken on the electrical items as seen on the equipment register spreadsheet.

WHAT'S IN PLACE:
On the Equipment Register - Under Column H / Next Risk Assessment Date - this is using a combination of index, match and max to find the equipment on the risk assessment spreadsheet - matching the Appliance ID but looking for the latest date and then I add the number of risk assessment days to that value from settings - this is done with the following:

=IF(
    AND(A4 <> "", G4 = "YES"),
    IF(
        INDEX('Risk Assessment'!$C$4:$AL$1048576, MATCH(A4, 'Risk Assessment'!$A$4:$A$1048576, 0), 1),
        MAX('Risk Assessment'!$A$4:$A$1048576 = A4, 'Risk Assessment'!$C$4:$C$1048576),
        "RISK NOT RECORDED"
    ),
    "NOT IN USE"
)

MY ISSUE:
From the Equipment Register Spreadsheet - Under the 'Result' column beside next risk assessment date - I want to retrieve the value of column 'Result' from Risk Assessment Spreadsheet but it needs to match both the appliance ID BUT it needs to be the most recent date to show the current Result.

WHAT I'VE TRIED:
Tried so many ways as this is one of the areas I'm trying to work on and spent so many hours looking at different ways and to no luck - I've electrical items to test but need to get these spreadsheets working and then I have a system in place not for myself but for compliance purposes too. My finance officer has suggest Vlookup but failing to see how this works as it needs to be recent date based on matching appliance ID.

BOTTOM LINE:
I do appreciate all the help but need to get this moving on - I used to program a lot back in my early days so for me to start doing this again at work - it's gone out of my mind but don't want to be spending too much time in one area otherwise the cost of spending more and more time on it becomes greater. Thank you for being amazing and awesome!

• excel

Share Improve this question Follow edited Jan 22 at 22:31 Oliver Jones asked Jan 21 at 11:02 Oliver JonesOliver Jones 322 bronze badges 4

• From your description it would seem you could combine the FILTER and MAXIFS functions to return the desired Result – Ron Rosenfeld Commented Jan 21 at 12:03
• Any examples to help me understand it better with what I've included (and edited thanks to who edited it better) – Oliver Jones Commented Jan 21 at 12:44
• Look at HELP for how to provide a proper example and provide it as text which can be copy/pasted. Edit the example into your question. – Ron Rosenfeld Commented Jan 21 at 14:13
• I've revised the question / layout of the purpose - hopefully this will make so much sense and hopefully get a better response out of it. – Oliver Jones Commented Jan 22 at 22:32

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Hard to give you a definitive answer since you have not posted a representative data set as text, however, the following should help.

Note that since your tags did not show any version constraints, the following requires 365 and will need some modification for earlier versions.

I used Tables with structured references as they are easier to work with, and you should be able to convert to normal addressing if you prefer (although I find tables much easier to work with.

I have assumed that the relevant dates are in Column 2 of the Risk_Assessment table, so you may need to change that.

In any event, the following, when entered into the CURRENT RESULT column of the EQUIPMENT_REGISTER table will return the Contents of the RESULT column in the RISK_ASSESSMENT table which matches both the ID in that same row of the EQUIPMENT_REGISTER table and the Maximum Date for that ID in the RISK_ASSESSMENT table.

=LET(
    a, FILTER(
        RISK_ASSESSMENT,
        [@[APPLIANCE ID/NUMBER]] = RISK_ASSESSMENT[APPLIANCE ID/NUMBER]
    ),
    b, XLOOKUP(
        MAX(CHOOSECOLS(a, 2)),
        RISK_ASSESSMENT[DATE],
        RISK_ASSESSMENT[RESULT]
    ),
    b
)

本文标签：