Excel VLOOKUP to compare 2 sheets

0
votes

I am trying to use a VLOOKUP to compare 2 sheets of data. I need to see if there is missing data in one of the sheets.

the VLOOKUP I have so far is

=IF(ISERROR(VLOOKUP(B3,Sheet1!$I$2:$I$2234,9,FALSE)),"MISSING FROM OTHER SHEET","")

But this just shows "MISSING FROM OTHER SHEET" for all rows, even though I have manually checked and can see some that are in both sheets.

Anybody any idea why this doesnt work?

B column contains a list of order numbers and the I column in sheet 1 contains a list of what appears to be the same order numbers. But with some discrepancies.

I am putting the formula into sheet 2, the column containing the order IDs in sheet 2 is "B", I am checking the data in sheet 1 to see if there are any missing orders in it and the column for order IDs in sheet 1 is "i"

I've checked some other answers here which is where I got my formula from but nothing really solves my problem.

2
excelvlookup

2 Answers

1
votes

Your table range Sheet1!$I$2:$I$2234 is INCORRECT.

Change it to account for the complete table

For example

Sheet1!$I$2:$Q$2234

EDIT(Followup From Comments)

and the column for order IDs in sheet 1 is "i"

In that case, keep the table range as it is and change ,9, to ,1,

Try

=VLOOKUP(B3,Sheet1!$I$2:$I$2234,1,0)

or

=VLOOKUP(B3,Sheet1!I:I,1,0)

Also ensure that the ID in Col B and the ID in Col I are same. If one of them has a space then Vlookup will fail. I have tested the above formula and it works.

0
votes
=IFERROR(VLOOKUP(B1,Sheet2!A:A,1,FALSE), "Missing")

Put the above formula in Sheet1 A:A, Values to be looked up are in Sheet1 B:B, The range to look them up in is Sheet2 A:A. Manipulate the data like that and it will work.

Result: It will return the value or it will say missing.