Forum Discussion
Return value form list based on value of another cell
Hi
Link to document: https://1drv.ms/x/s!AipojThikVxRgiRC0t__L2g9jnrH?e=6f584M
I have a list of two columns (in sheet Award Data):
Column 1 is Awards
Column 2 is hours.
Each award is given once a certain number of hours is reached.
On worksheet "CUA Log" is a log of activities that results in a cell that contains total hours recorded (F4).
I want cell I4 on CUA Log to indicate which award has currently been gained based on the total hours cell (F4). As more activities are added, the total hours increases and the cell indicating the current award gained should change when the appropriate number of hours has been reached.
I currently use a nested If/Then statement but the lists are over 30 rows long and may change so I am interested if there is a neater dynamic approach.
Thanks
You can use
=INDEX('Award Data'!A2:A31,MATCH(F4,'Award Data'!B2:B31))
If you have Microsoft 365 or Office 2021, an alternative formula is
=XLOOKUP(F4,'Award Data'!B2:B31,'Award Data'!A2:A31,"",-1)
By the way, the formula in K6 can be shortened to
=MIN(SUMIF(C:C,I6,D:D),10)
3 Replies
Is it possible for you to post a copy of the actual workbook? Use OneDrive or GoogleDrive, posting a link here so we can open it. Just make sure no actual names of real people (or other confidential/private info) is included. That would help get a more complete idea of what you're working with.
I've added it to the OP and updated references etc.
ThanksYou can use
=INDEX('Award Data'!A2:A31,MATCH(F4,'Award Data'!B2:B31))
If you have Microsoft 365 or Office 2021, an alternative formula is
=XLOOKUP(F4,'Award Data'!B2:B31,'Award Data'!A2:A31,"",-1)
By the way, the formula in K6 can be shortened to
=MIN(SUMIF(C:C,I6,D:D),10)
