Log In
Register
Home
New Member
September 19, 2019
Offline
I have 4 filled big columns (2000 rows each) filled like below:
| Column A | Column B | Column C | Column D |
| 3024 | 1 | 2 | 0 |
| 3001 | 1 | 2 | 0 |
| 3020 | 1 | 1 | 0 |
| 3002 | 1 | 1 | 0 |
| 3021 | 1 | 1 | 0 |
| 3020 | 1 | 1 | 0 |
| 3020 | 1 | 1 | 0 |
| 3020 | 1 | 1 | 0 |
And I have the same 4 big columns (2000 rows each) in a different sheet. 1st sheet is fully filled (all 2000 rows). This second sheet has only 1st column filled, and rest 3 empty. I was looking for a formula that would filled the blank cells based on the corresponding value in Column A (matched with First table)? Like for Column A value 3002, what would be the value in column B, c and d?
| Column A | Column B | Column C | Column D |
| 3002 | ? | ? | ? |
| 3003 | |||
| 3098 | |||
| 3009 | |||
| 3010 | |||
| 3334 | |||
| 3233 | |||
| 3023 |
Urgent help would be appreciated as I am stuck in office and its already 8.28 pm. Thanks. God bless.
October 5, 2010
Offline
Hi,
If your data starts in A1 on both sheets then you can make use of implicit intersection and the formula
=INDEX(Source[[Column2]:[Column4]],MATCH($A1,Source[Column1],0),0)
where Source is a table on Sheet1 containing your source data. I'm using Structured References to refer to the data.
If you have Dynamic Arrays (available in Office 365) then you only need to enter this formula into the first column of each row on Sheet 2 i.e. B1, B2 etc
If you don't have dynamic arrays then you'll need to enter it into each cell you want to populate on each row i.e. B1, C1, D2 and then B2, C2, D2 etc
If you look at the attached workbook you'll see that I have included an example of each on Sheet 2. Row 1 has the formula in B1, C1 and C2.
But on row 2 I've only entered it in B2.
Note : You have multiple rows on the first sheet that have the same value in ColA e.g. 3020. The above formula matches the first occurrence of a number in ColA
Regards
Phil
1 Guest(s)
