Page 2 of 2 FirstFirst 1 2
Results 11 to 14 of 14

Thread: Create a Lookup over multiple worksheets

  1. #11
    Seeker Jester's Avatar
    Join Date
    Dec 2019
    Posts
    7
    Articles
    0
    Excel Version
    2007


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, the formula in the spreadsheet that you've attached reads;

    =_xlfn.IFNA(VLOOKUP(F22,INDIRECT("'"&E22&"'!$A$4:$B$1200"),2,0),"No")

    No matter what data I select from the 2 drop down boxes you've created, the result reads '#NAME?' which suggests an error in the formula - but having never used IFNA or INDIRECT, I'm not sure where I should be looking to put in any amendments. I tried it first on the Crown and Half Crown tabs you've done and then on other tabs, but the result is always the same. Yours - Baffled and Puzzled, Glenn

  2. #12
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    It will be the IFNA function - sorry.

    Try this:

    =IF(ISNA(VLOOKUP(F22,INDIRECT("'"&E22&"'!$A$4:$B$1200"),2,0)),"No",VLOOKUP(F22,INDIRECT("'"&E22&"'!$A$4:$B$1200"),2,0))
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #13
    Seeker Jester's Avatar
    Join Date
    Dec 2019
    Posts
    7
    Articles
    0
    Excel Version
    2007
    Brilliant! That's what I need it to do, thank you so much. I've been googling the indirect and ifna functions to try and get a better understanding and will now save this spreadsheet in 2 places so I've got a backup if I mess things up. Now off to play a bit more. Merry Christmas, kind regards, Glenn

  4. #14
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    Glad to have helped!
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 2 of 2 FirstFirst 1 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •