I have a file.xls with three sheets.
Sheet1, 2 columns, 3000 rows;
- ColumnA: location_id
- ColumnB: location_label
Sheet2, 2 columns, 5000 rows;
- ColumnA: location_id
- ColumnB: screen_id
Sheet3, 2 columns, 6000 rows;
- ColumnA: screen_id
- ColumnB: screen_name
how to group data into a new sheet4 with the following syntax (view image);
- ColumnA: Location_label
- ColumnB: screen_name
location_id get location_label name in sheet 1, location_id get screen_id value in sheet2, screen_id get screen_name value in sheet3 and in sheet4 result with location_label and screen_name.
#EDIT QUESTION WITH USE VLOOKUP;
I tried to use VLOOKUP but from error after the first id number 19 of sheet2...i have used this '
=VLOOKUP(Sheet2!A2;Sheet1!A2:B2133;2;)
i get
- RED
- GREEN
- YELLOW
- #N/D
- #N/D
CodePudding user response：
Well I'm not the best with Excel and I do not fully understand what you want as an end result. But here is my guess what I think is the problem.
I suspect that in sheet 4 you type you code in A2 and use your mouse to drag the formula to the bottom of the column. If this is the case, your formula will not be exactly the same in every cell, because your matrix in the formula will change while you drag your mouse. Therefore the error #N/D
To prevent your matrix from changing while dragging your mouse you should use '$'.
So your formula would be:
=VLOOKUP(Sheet2!A2;Sheet1!$A$2:$B$2133;2;)
Possibly a tip: I see you have the same columns in more than 1 sheet. You can put all you data in 1 sheet and then use filters to select the data you want. See this article.