2 criteria for a VLookup
I have two sheets, 1 pipeline 2 install bookings.
On the install bookings sheet there is a job number column and a install status column
I have done a VLOOKUP in the pipeline that searches in the Install bookings for the matching row job number and returns the install status for that job number.
=IFERROR(VLOOKUP([Pronto JN:]@row, {Install Req JN}, 2, false), "")
however there may be times that 2 or more install bookings are made against a specific job number. I would like to figure out how to have the install status returned for the most recent entry in the install bookings for a particular job number.
I don't know if this can be done using MAX and a date column or auto numbering the entries and using LARGE to find the matching PO and the largest number? cant seem to figure it out
not sure if this makes sense, hopefully it does
Answers
-
How are new entries being made on the first sheet?
-
Hey Paul,
The Pipeline they get entered manually, the install bookings is filled out using a form.
-
My suggestion would be to insert an auto-number column and sort the sheet in descending order so that most recent entries are at the top of the sheet. Then set your form to populate at the top of the sheet to keep new entries populating.
Since the formula pulls the value from the first match, having new entries made at the top of the sheet means your most recent entry will be your first match.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!