Share via

XLOOKUP() where the lookup value has multiple occurrences in the lookup array

Anonymous
2020-05-29T09:20:31+00:00

Hi, this question may have a fairly simple answer, but I haven't managed to figure this out using XLOOKUP() - or another function.

Example formula would be: XLOOKUP(1,A:A,G:G) - the function is searching for the number 1 in column A then returning a value from column G.

My question is, how do I tell it to keep searching and return the values in column G for all instancesof '1' in column A, rather than stopping the first time it encounters '1' in column A?

Many thanks in advance for any assistance anyone could offer!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-05-29T11:09:39+00:00

    Hi William,

    Indeed, the XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds.

    If you want to return multiple instances match list using formula, we recommend using the INDEX, SMALL and ROW functions.

    Here is my test result: 

     

    You can change the data range based on your requirement. Moreover, you can check and follow the steps in this article. Hope it could help. 

    Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the download you receive from the third-party linked sites or any support related to the download or the downloaded technology. If you need support relating to the third-party technology, please contact the author directly.

    Best Regards,

    Ivy

    50+ people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-05-29T12:25:11+00:00

    Dear William,

    I am glad to know that my suggestion is helpful to you.

    You are always welcome to post in our forum if you have any concerns when using Microsoft 365 products. We will be here for you all the time.

    Take care and stay safe!!

    Best Regards,

    Ivy

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2020-05-29T12:05:18+00:00

    Hi Ivy, thank you so much for helping me, it is very much appreciated.

    1 person found this answer helpful.
    0 comments