Finding missing data from one sheet to another using vlookup.

joyguru

New member
Joined
Jan 22, 2019
Messages
9
Reaction score
0
Points
0
Excel Version(s)
2007, 2010
Say I have a workbook with 3 sheets shown as below:

Sheet1
1.png

Sheet2
2.png
Sheet3
3.png

Now, I want to fill the missing data in sheet2 from sheet1 using vlookup. I've tried the below formula after selecting the blank cells in sheet2
Code:
=VLOOKUP(Sheet1!$A$2:$A$51,Sheet1!$A$2:$F$51,COLUMN(),0)
and it gets the job done but it does not work for sheet3.
How can I do that? Can I use COLUMNS() formula instead of COLUMN() in some way
BTW, I was asked this in an interview.
 

Attachments

  • Book1.xlsx
    15.1 KB · Views: 17
As this is an interview question, you really need to sort it out yourself. It would not be appropriate for us to give you an answer - you need to be able to understand what is happening, and therefore working it out for yourself is critical.

I will give you one clue: without a unique identifier for each salesperson that is already in place on ALL sheets, you will never be able to make VLOOKUP or INDEX MATCH work on their own. You might need to learn VBA.
 
Can I use COLUMNS() formula instead of COLUMN() in some way.
If you did not think that we did not want to help you, here are two formulas.

This ARRAY formula below shows all values from Sheet1 from the 'A' column missing in 'A' column on Sheet2. (this CSE formula, need be finished with Ctrl+Shift+Enter)
Code:
=INDEX(Sheet1!$A$2:$A$51;SMALL(IF(ISNA(MATCH(Sheet1!$A$2:$A$51;$A$2:$A$51;0));ROW($A$2:$A$51)-ROW($A$2)+1);ROWS(H$3:H3)))
This formula all the numbers that exist in the 'A' column on Sheet1, but there are none in the column 'A' on Sheet2 (pay attention that the cell range must be identical, '$A$2:$A$51').
The formula is based only on the first two arguments, in the INDEX function syntax =INDEX(array;row_num;column_num).
Instead of the "column_num" argument, nested is the SMALL function, this function/formula returns the number of row in which the missing number on Sheet2 appears in the 'A' column.
To make it clearer, use the Evaluate formula and the 'F9' key at, to view the calculation.

Furthermore, using the VLOOKUP formula, this formula returns a value from a certain row for a given range if a certain condition is match. (This formula works only with unique values as a condition, so it does not support duplicate as a condition).
Code:
=VLOOKUP($H2;Sheet1!$A$2:$F$51;COLUMN(B$1);FALSE)
Also, notice that within the Vlookup formula (instead of the "col_index_num") argument, nested is COLUMN() function. This function/formula, when copying to across, changes the number of columns from which it returns the result.
This tutorial of the COLUMN function and COLUMNS() function is not in English, but the image can help you understand the return of the function result when copying the formula.
And finally, you need to pay attention to the absolute and relative Excel address if you copying the formula.

If you want to avoid #NUM! errors, then the formula neste in IFERROR or IF/ISERROR (Depending on the version of Excel you are using. See note and syntax functions in attacheed file)
To make it easier for you, if you have data on two worksheets (Sheet1 and Sheet2), you first do everything in one worksheet (for example, Sheet1), Then, with the command 'Select/Cut/Paste' all the results you move to Sheet2. Excel will automatically into the formula set the relevant 'Sheet Name Tab' after the paste command.

I hope I helped you learn something new?
For more details and the necessary knowledge, as she said colleague @AliGW, you need to learn the basics of Excel and here you can find some instructions for beginners in Excel with example formulas.

I hope you asked for this, if you did not, I apologize for my misunderstanding.
 

Attachments

  • joyguru-navic9837.xlsx
    20.9 KB · Views: 15
Back
Top