The Vlookup Function That Searches The Entire Workbook in Excel - UDF is fantastic and will prove useful, however, in this particular case, I need to add an IF statement and/or condition. I am searching for a value of a standard field for all sheets across multiple sheets and need to be able to make it only find the result if another field matches. Example, all worksheets have individual customer names in C2 and a score result for each period in D9 and potentially E9 with E being the most recent if available, otherwise D would be. I need to bring back the most recent score for a each customer on the summary page. I can't use the custome Vlookup because it will always find the score field on the first page and bring that result back regardless of the customer I am actually trying to search for. The amount of sheets varies each month so creating a list of them isn't really practical so if I can add IF statements, etc that would be perfect. The index match would be preferable for me as it is more flexible/customizable, but I'm not sure if I can change the UDF to use that forumla instead of Vlookup. Any suggestions?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How to use the Vlookup Function That Searches The Entire Workbook in Excel - UDF with a condition or if statement
0
Discussion
Would it work for you if the function just searched through the workbook in reverse and returned the last result for the match? If so, that's an easy fix.
don (rep: 1989) Apr 1, '19 at 4:07 am
Thank you for responding, I appreciate it :). Unfortunately that won't work either, as there could be 20+ sheets that all contain the same fields so it would just find the last sheet and keep bringing those back. All the sheets, except the summary sheet, are templates that have the same data fields. Ex. Customer Name, Customer Number, Revenue, Risk Score, etc. The results are specific to the customer on each sheet and that is what I am trying to be able to bring back, what each customer scored for the Risk Score for example.
Achamp24 Apr 1, '19 at 9:31 am
So bring back the latest score for each sheet?
don (rep: 1989) Apr 12, '19 at 3:00 am