Forum Discussion

Colinahn's avatar
Colinahn
Copper Contributor
Nov 17, 2020
Solved

I really need a help with finding & matching data from two different sheets.

Hi,   I'm trying to search and return the data from two different sheets by using excel formulas such as xlookup, index & match, and so on. What I want to do is that I want to find the PO# value t...
excel
Formulas and Functions
Macros and VBA
Colinahn's avatar
Colinahn
Copper Contributor
Nov 18, 2020
Hi, Thanks for the reply.
I tried (literally copied and pasted) your formula, but it showed #NUM error.
Any reason why?

adversi's avatar
adversi
Iron Contributor
Nov 18, 2020

Colinahn 

You have to make sure the ranges are linking to the appropriate sheet and since these are arrays, press Ctrl + Shift + Enter to see the curly brackets { } surrounding the formula. If you continue to have issues, send over your file and I can help apply it

  • Colinahn's avatar
    Colinahn
    Copper Contributor
    Nov 18, 2020

    adversi Hi I uploaded a sample worksheet. I was able to return a value of cell C3 by using your formula, but when I applied to other cell, it returned a wrong value (cell E3 and C5). I'd be really appreciate if you could help finding what went wrong and fixing issues.

    • adversi's avatar
      adversi
      Iron Contributor
      Nov 18, 2020

      Colinahn 

      here you go - the issue was the Countif reference, it should have been the qty figure rather than the product code 

      • Colinahn's avatar
        Colinahn
        Copper Contributor
        Nov 19, 2020

        It works perfectly! Thank you so much for your help.
        If you don't mind, could you please tell me how this could work?

         

Resources