Forum Discussion

certavi's avatar
certavi
Copper Contributor
Feb 22, 2020
Solved

INDEX MATCH function, avoid duplicate returns

Hi everyone!   I'm working on a sheet for work. I am on my last task and can't seem to figure this one out. I'm hoping someone can help:   I have a data sheet with all the data. In my current she...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 22, 2020

    certavi 

    If you are on Excel with dynamic arrays that could be

    =UNIQUE(FILTER('Horse data'!B2:B100,
  (I$13='Horse data'!C2:C100) * ($K$13='Horse data'!D2:D100)
))
Brad Yundt's avatar
Brad Yundt
MVP
May 04, 2022
=IFERROR(INDEX('Walleye JPMcopy)'!L$2:L$643,AGGREGATE(15,6,(ROW('Walleye JPMcopy)'!D$2:D$643)-ROW('Walleye JPMcopy)'!D$2)+1)/('Walleye JPMcopy)'!D$2:D$643="OPTIONS"),ROWS(D$2:D2))),"")
cweaver345414's avatar
cweaver345414
Copper Contributor
May 04, 2022

Brad Yundt Thank you for putting that together but it doesnt look like a value is pulling up, there is just a blank screen. Do you know how I would be able to send this file so you could possibly look at it?

  • Brad Yundt's avatar
    Brad Yundt
    MVP
    May 16, 2022
    It's confusing when people jump into an already answered question and start asking a new one. Best practice is to post a new question.

    The formula will be easy if you post a sample workbook, a description of what you want the formula to do and expected result. If you have a formula that isn't working the way you would like, put that in the workbook too so we can teach you the necessary tricks.
  • jrayluv's avatar
    jrayluv
    Copper Contributor
    May 16, 2022
    this awsome sir.
    how to add more criteria like: <100 sir ?
    im stuck this formula. thanks for help again.
  • byundt's avatar
    byundt
    Brass Contributor
    May 05, 2022

    cweaver345414, 

    This was a 5 minute problem once you posted your file and said which version of Excel you use. 

    My pleasure to help. 

    Brad

  • cweaver345414's avatar
    cweaver345414
    Copper Contributor
    May 04, 2022
    Wow Brad, thank you. Do you have a Venmo or something? I definitely would like to buy you some beer for helping me out.
  • Brad Yundt's avatar
    Brad Yundt
    MVP
    May 04, 2022

    cweaver345414,

    Thank you for posting the file. Without it, I couldn't have figured out the problem.

     

    There were actually two issues. First, the name of the worksheet wasn't Walleye JPMcopy) but rather Walleye JPMcopy (without the right parenthesis). Second, you had a dozen or so spaces following the text in Walleye JPMcopy column D. Deleting those trailing spaces made the formula as suggested work correctly.

     

Resources