Usually when you're doing lookups in Excel, it brings back the first value that matches. But what if you want the last value and did not want to sort the table to do that? You can actually perform this type of lookup quite easily in Office 365 version of Excel with the XLOOKUP function. But what if you don't have O365? Well you're not stuck, cause you can combine some functions (INDEX, LARGE, IF and ROW) to do the same thing. I'll show examples of both and offer a bonus on how to create a drop down list near the end.
Just found your site a couple of days ago. I have learned a few things I did not know. Thank you for that. On this video, I wanted to mention that I know another way to find the last value using the MATCH function. If the 3rd argument is left blank, it defaults to 1, which forces MATCH to search bottom to top. So, per your example file, it would be =INDEX($A$2:$A$15,MATCH(2,1/($A$2:$A$15=$H$3)))
ReplyDelete👍👍👍
ReplyDelete