Forum Discussion
Index and Match
Can someone help me with my issue?
I only want to display companies from sheet one on sheet 2 that have a match for cell A1 on sheet 2.
Current workbook formulas.
Sheet 1 last column, =IF(ISNA(MATCH(C2,{"abc","abc1","abc2","abc3"},0)),"-",MAX(I$1:I1)+1)
| Date | serial number | company | Review Date | Status | ||||
| 5/1/2018 | B12345 | ABC | 5/1/2018 | Accepted | 1 | |||
| 5/2/2018 | B23456 | ABC1 | 5/2/2018 | Consider | 2 | |||
| 5/3/2018 | B34567 | ABC2 | 5/3/2018 | Compare Elsewhere | 3 | |||
| 5/4/2018 | B45678 | ABC3 | 5/4/2018 | Rejected | 4 | |||
| 5/5/2018 | C12345 | ABC | 5/4/2018 | Accepted | 5 | |||
| 5/5/2018 | C23456 | ABC1 | 5/2/2018 | Consider | 6 | |||
| 5/5/2018 | D12345 | ABC2 | 5/3/2018 | Compare Elsewhere | 7 | |||
| 5/5/2018 | D24567 | ABC3 | 5/4/2018 | Rejected | 8 |
Sheet 2
Each data point in all three columns
=IFERROR(INDEX('Sheet 1'!A:A,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")
=IFERROR(INDEX('Sheet 1'!B:B,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")
=IFERROR(INDEX('Sheet 1'!C:C,MATCH(ROWS($2:2),'Sheet 1'!$I:$I,0)),"")
| ABC | ||
| date | serial number | Company |
| 5/1/2018 | B12345 | ABC |
| 5/2/2018 | B23456 | ABC1 |
| 5/3/2018 | B34567 | ABC2 |
| 5/4/2018 | B45678 | ABC3 |
| 5/5/2018 | C12345 | ABC |
| 5/5/2018 | C23456 | ABC1 |
| 5/5/2018 | D12345 | ABC2 |
| 5/5/2018 | D24567 | ABC3 |
Thank you in advance.
Josie
=IFERROR(INDEX('Sheet 1'!A2,MATCH($A$1,'Sheet 1'!$C2,0)),"")Unfortunately from your initial question i didn't understand that there are 48 sheets in your workbook. I tried above formula for several companies and sheets in the attached file and it seems to work.
4 Replies
=IFERROR(INDEX('Sheet 1'!A2,MATCH('Sheet 2'!$A$1,'Sheet 1'!$C2,0)),"")Is this what you are looking for?
- it is closer to what i need. i have 48 companies. there will be a worksheet for each company. the first sheet will have all the companies and their rows in a data dump. i need to see the data for each company on each sheet. every sheet will have the company name in cell A1. your solution only shows the same company on every sheet.
=IFERROR(INDEX('Sheet 1'!A2,MATCH($A$1,'Sheet 1'!$C2,0)),"")Unfortunately from your initial question i didn't understand that there are 48 sheets in your workbook. I tried above formula for several companies and sheets in the attached file and it seems to work.
