Hi,
I try to show most recent date in Sheet2 from Sheet1. I get N/A error. Please see file attached. Can figure out what is wrong.
Thank you.
Hi,
I try to show most recent date in Sheet2 from Sheet1. I get N/A error. Please see file attached. Can figure out what is wrong.
Thank you.
In the Vlookup you are trying to max the column reference and it is not found.
Try this CSE Formula:
=LARGE(IF(Sheet1!B1:B4=Sheet2!A1,Sheet1!C1:C4),1)
Enter this in A2 then press Ctrl+Shift+Enter to get the brackets around it.
You could also sort the list in sheet1 by date in descending order, that way the first date that the vlookup finds will be the most recent date.
A MAX array function would also work just as well:
=MAX(IF(Sheet1!B:B=A1,Sheet1!C:C))
After typing/editing that formula, press CTRL+SHIFT+ENTER. When done correctly, Excel will automatically insert braces, { }, around your formula. (Don't insert braces yourself.)
There is perhaps also the non-array alternative of:
=MAX(INDEX((Sheet1!B1:B4=A1)*(Sheet1!C1:C4),0))
(may be slower than an Array if used on very large data sets)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks