Excel Magic Trick 558: VLOOKUP to verify if Whole Record is in Table

[1002vicky]

I have a master file and a get data file by the name of sales indents.

Now when i pulled the data from the master file. i put the command as :

"=IF([MasterFile.xlsx]Cust_Lis­t!A2=" "," ",[MasterFile.xlsx]Cust_List!A­2)"

"I got the data perfect.

But when i saved and closed the sheet, I was shocked to find out this.

"=IF('C:\Users\Vicky\AppData\R­oaming\Microsoft\Excel\[Master­File.xlsx]Cust_List'!A2=" "," ",'C:\Users\Vicky\AppData\Roam­ing\Microsoft\Excel\[MasterFil­e.xlsx]Cust_List'!A2)"

[ExcelIsFun]

What is your question?

[MasterFile.xlsx]Cust_Lis­t!A2

has to turn to this:

'C:\Users\Vicky\AppData\R­oami­ng\Microsoft\Excel\[Master­Fil­e.xlsx]Cust_List'!A2

or else it would not know where to look!

The two workbooks are connected and communicating with each other because the file knows where it is saved!

[Thahzeeb]

HI Mike,

Just another help.

Suppose I have two colums,say Column A and Column C, then how do I check that all the values present in Column A is also there in Column C ?

Earlier I used the ' = ' to check but this does not help if the values in Column C are not in the same order as Column A.

[ExcelIsFun]

I do not understand your question.

[ExcelIsFun]

I have a playlist with a few videos about determining whether or not an item is in another list. Search for this playlist title:

'Excel Compare / Merge Two Lists'

Then click on the playlist in the list of returned links.

[Valkyries101]

How about using SUMPRODUCT , IF formulas for example:

=IF(SUMPRODUCT((A1:A3=A6)*(B1:­B3=B6)*(C1:C3=C6))>0;"True";"F­alse")

when the table in A1:C3 and testing cells in A6:C6

[ExcelIsFun]

Sure!

[mikeyroro]

vlookup with a helper cell is clutch! Thanks Mike

[ExcelIsFun]

You are welcome!

Clutch!! I love that word!