CODEWITHSUNDEEP
excelexcel-formula
Tobi
Tobi

Reputation: 89

Excel - issue with VLOOKUP formula - it doesn't pick dates

I need help with the below formula. I already tried to find a solution for this problem but no success.

If account number exists in column A in the 'Returns' tab and also in column A in the 'July Sales' tab, then I need to get date from the column B in 'Returns' tab.

I manually checked a few accounts on both spreadsheet and find some duplicates.

My formula is as follows:

=VLOOKUP(Returns!A:B,A:B,2,0)

Screenshots:

enter image description here

enter image description here

I tried to change format to text/general, text to columns and trim function but it's still not working.

Also, as I have over 200k rows in each table, can I use any different formula instead to speed this up?

Finally, is there any way to pick dates only if these are within 30 days

Thanks in advance.

Upvotes: 0

Views: 2495

Answers (4)

Gravitate
Gravitate

Reputation: 3064

I highly recommend an INDEX/MATCH combination over a VLOOKUP. It is MUCH faster, particularly if you are working with a large number of rows. It may even be faster than the COUNTIF solution suggested by @ForwardEd.

=IFERROR(INDEX(Returns!$B:$B,MATCH($A2,Returns!$A:$A,0)),"")

Upvotes: 1

Forward Ed
Forward Ed

Reputation: 9874

=IF(COUNTIF(RETURNS!A:A,A2)>0,B2,"NO RETURN INFO")

Not sure what you want done when the account is not found on the RETURNS worksheet. Change "NO RETURN INFO" to what ever text you want including "" for a blank. Make sure you apply the same format for cells in column F as you do in column B. Copy the above formula down as required.

POC

POC2

Upvotes: 3

ChrisCarroll
ChrisCarroll

Reputation: 463

try the below, which will return blanks for non-matches as opposed to errors;

=IFERROR(VLOOKUP($A2,Returns!$A:$B,2,FALSE),"")

Upvotes: 2

jsheeran
jsheeran

Reputation: 3037

You're using Returns!A:B as your lookup value, which doesn't make sense. Instead, try the following:

=VLOOKUP([@Account], tblReturns[[Account]:[Submit_Date]],2,FALSE)

where tblReturns is the name of the table on your Returns worksheet.

I've made the assumption that you're working with tables, since the data in your screenshots is formatted like the default table. If they're just normal ranges, the equivalent is

=VLOOKUP($A2,Returns!$A:$B,2,FALSE)

Upvotes: 3

Related Questions