SOLVED

Vlookup not finding certain values

%3CLINGO-SUB%20id%3D%22lingo-sub-1619392%22%20slang%3D%22en-US%22%3EVlookup%20not%20finding%20certain%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1619392%22%20slang%3D%22en-US%22%3E%3CP%3EI%20exported%202%20transaction%20detail%20reports%20from%202%20separate%20QuickBooks%20files%20representing%20all%20the%20transactions%20involving%20the%20Accounts%20Payable%20general%20ledger%20accounts.%26nbsp%3B%20This%20exercise%20is%20to%20discover%20transactions%20in%20one%20file%20that%20are%20not%20contained%20in%20the%20other%20file.%26nbsp%3B%20I'm%20using%20Vlookup%20but%20perhaps%20this%20is%20the%20wrong%20approach.%26nbsp%3B%20Row%207317%20is%20just%20one%20example%20but%20there%20are%20many%20rows%20returning%20%23N%2FA%20that%20should%20find%20a%20corresponding%20value.%26nbsp%3B%20You%20can%20see%20in%20the%20other%20file%20(tab)%20that%20the%20corresponding%20transaction%20exists%20(row%207351).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20there%20is%20the%20issue%20of%20how%20Vlookup%20handles%20duplicate%20amounts%20in%20multiple%20rows%2C%20Will%20Vlookup%26nbsp%3B%20always%20return%20the%20first%20row%20it%20finds%20working%20from%20top%20to%20bottom%20in%20the%20target%20area%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1619392%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1619548%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20not%20finding%20certain%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1619548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774716%22%20target%3D%22_blank%22%3E%40mossdata%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3EYou%20can%20see%20in%20the%20other%20file%20(tab)%20that%20the%20corresponding%20transaction%20exists%20(row%207351).%26nbsp%3B%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EYour%20VLOOKUP()%20only%20searches%20up%20to%20row%207047.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1619567%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20not%20finding%20certain%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1619567%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3BThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I exported 2 transaction detail reports from 2 separate QuickBooks files representing all the transactions involving the Accounts Payable general ledger accounts.  This exercise is to discover transactions in one file that are not contained in the other file.  I'm using Vlookup but perhaps this is the wrong approach.  Row 7317 is just one example but there are many rows returning #N/A that should find a corresponding value.  You can see in the other file (tab) that the corresponding transaction exists (row 7351). 

 

Then there is the issue of how Vlookup handles duplicate amounts in multiple rows, Will Vlookup  always return the first row it finds working from top to bottom in the target area?

2 Replies
Highlighted
Best Response confirmed by mossdata (New Contributor)
Solution

@mossdata 

You can see in the other file (tab) that the corresponding transaction exists (row 7351). 

 


Your VLOOKUP() only searches up to row 7047.

 

 

Highlighted