MLookup:How to use

[jomili]

I've copied the code from http://www.excelfox.com/forum/f12/vl...iple-values-4/, but I'm restricted from downloading the example. I can't figure out how to use the function as a lookup, because I'm not sure what the NthMatch is supposed to be. Also, I'm assuming this can used to look in multiple directions, but have no idea of how to set the directionality. Any help is greatly appreciated.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9iMmBDtf4m1
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxFIZ858qf7w_uA9bd4AaABAg.9dKpEpUk3YT9d VEGnka6yj
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9brzh_99JF9
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9bsrQIgXb3L
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmx0REIz41
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9bmyko2YUvQ
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmzpPqfLRD
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZwbV_Y_7UFzHwNBh4AaABAg.9dKb0Vc7MOB9d VK8si3ont
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugx6Ec_r4kb9EYOVgIt4AaABAg.9dOW613fb8V9d VIJECZIdC
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g 9cLjhPiaz
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZy1NAMBx5Uv4U2cJ4AaABAg.9f0XX-_JaGp9g9bYLMZiIy
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyL-xp8IiiahmQ12kJ4AaABAg.9f7xHCpAEx29g9asFhVFfT
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxRxyFNNp3WHTzuiJJ4AaABAg.9fFR6ECmXk69g 9afNBcS4Z
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwsdMh0FGDfvA249_B4AaABAg.9fLR6FHCIVI9g 9aLlUyzog
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g 9_4422NzK
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugwyy8JXr56HJ8m_od94AaABAg.9gSFgqqJQNV9g TXco41b5l
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TYl6RldpA
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TfhAWU9ju
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TfuYQGmUa
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g Tg3AmMPUc
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9g TgEqh5wdo
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxmUK0S_aZVZWz8-gt4AaABAg.9gLc3DfWfHl9gTZ3y6fL1H
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZloYeY2wQr7-xTOh4AaABAg.9gB2bbbs9mB9gTZUkNYI8e
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzlM96nGEhW9J1Gpgd4AaABAg.9fmOFVcXZh49g T_8CYeQgz
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Ingolf]

Hi,

You will see the explanation in attached pictures.

MLookup.jpg

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[cwmichael81]

Great function, and was able to use exactly how I wanted to. However, I'm working with 15,000+ lines of data, and it's returning all the duplicates as well. Is there a way to modify, so it only returns a unique value once?

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Admin]

Hi

try this http://www.excelfox.com/forum/f12/fu...nate-values-3/

[cwmichael81]

Great function, but I don't think it will help me in this situation. I've used the Mlookup function to compare columns between two different workbooks. It looks up a job ID in the table, and returns the name of the company in the next column. I used Mlookup because the same job ID can be associated with more than 1 company. It finds the job ID, and gives me all the different company names it is associated with.

For example, Mlookup browses through 15k+ lines of data, and will return in one cell "Company A, Company B, Company A, Company A, Company F, Company E, Company F, Company Z"

So what I gain from that is that job ID is associated with only 5 companies. I don't want it to return every single instance of when it appears. Does that help?

[Admin]

Hi

Attach the workbooks with expected results. May be we can provide VBA code to do the trick.