I am trying to get the below formula to provide me the values from column A that have the matching program key found in the AF column. I'm trying to have ALL the values that match show up, and not just one.
=ARRAYFORMULA(VLOOKUP(dts_program_key,{'CMT Upload'!AF2:AF,'CMT Upload'!A2:A},2,0))
dts_program_key = 926d4c81-cf34-4299-abcc-23ba22a6dbe9
The "CMT Upload" tab that the data is being pulled from:
| Contract Lane Key (Column A) | Program Key (Column AF) |
|---|---|
| TestA | 13d9c80a-0a09-4e84-8157-94a40c04d1fe |
| :---- | :------: |
| TestB | 926d4c81-cf34-4299-abcc-23ba22a6dbe9 |
| :---- | :------: |
| TestC | 0e2183bc-d2ee-4835-836c-b117024b6d09 |
| :---- | :------: |
| TestD | 926d4c81-cf34-4299-abcc-23ba22a6dbe9 |
| :---- | :------: |
| TestE | 926d4c81-cf34-4299-abcc-23ba22a6dbe9 |
In the above example, the vlookup should provide TestB, TestD, and TestE, all without any empty rows in between. Any help is appreciated.
CodePudding user response：
VLOOKUP is designed to only return the first match. If you want to return all the matches, use FILTER instead.
=FILTER('CMT Upload'!A2:A,'CMT Upload'!AF2:AF=dts_program_key)