I have one sheet that has IDs for which I'd like to pull information about from another sheet.
Sheet 1 with IDs:
| Id |
|---|
| A |
| B |
Sheet 2 with additional information:
| Id | Amount |
|---|---|
| A | $500 |
| A | $600 |
| A | $700 |
| B | $200 |
| B | $300 |
| B | $400 |
| C | $30 |
| C | $40 |
| C | $50 |
I would like returned all information from Sheet 2 that matches all the IDs in Sheet 1. The result I want:
| Id | Amount |
|---|---|
| A | $500 |
| A | $600 |
| A | $700 |
| B | $200 |
| B | $300 |
| B | $400 |
I have tried using =FILTER(Sheet2!A:B,Sheet1!A2=Sheet2!A:A) but that only gets me the first ID:
| Id | Amount |
|---|---|
| A | $500 |
| A | $600 |
| A | $700 |
I've played around with changing Sheet1!A2 in the filter formula to Sheet1!A:A but that throws an error. How can I get information for all IDs? Help!
CodePudding user response：
You can try with FILTER and REGEXMATCH:
=FILTER(Sheet2!A:B, REGEXMATCH (Sheet2!A:A,TEXTJOIN("|",1,Sheet1!A:A)))
CodePudding user response：
Try FILTER() with COUNTIFS().
=FILTER(Sheet2!A2:B,INDEX(COUNTIFS(A2:A,Sheet2!A2:A)))