Forum Discussion

benhollister's avatar
benhollister
Copper Contributor
Oct 30, 2022
Solved

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...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Oct 31, 2022

    benhollister 

    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)

mathetes's avatar
mathetes
Gold Contributor
Oct 30, 2022

benhollister 

 

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.

  • benhollister's avatar
    benhollister
    Copper Contributor
    Oct 31, 2022

    I've added it to the OP and updated references etc.

    Thanks

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 31, 2022

      benhollister 

      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)

Resources