Lookup Function

[shawnr72]

I have attached a very very simple excel file that I have a question about. We enter job numbers in a column and then to the right of it put a PO number. Right now we have to sort the job number list to see what the last PO for that specific job was since each job the PO numbers start at 1 and go up. So we may have CP11812-1 for one job and then CP11645-1 for the other. We will have thousands of these as all are items go into this spreadsheet.

What I would like to know is if I could have the PO # auto populate based on the job number. If you look at my example I have typed CP11645 in Cell C9. If I look back at some of my previous PO's the last number I used for CP11645 was 43 so my next one would be 44. This is simple to see in this list, but as I mentioned, we have thousands of these and some jobs are pages back and would take a little while to find. I know that we could setup as a table and select just that job, but trying to do in one step. Maybe it is not possible, but when I put the job number in I want Excel to go over to the PO number and search up till till it finds the number of the last PO that matches the job number and then add 1.

Is this even possible?

[JieJenn]

You can probably insert a helper column and indicating what the next PO# will be. In D3, enter this formula with Ctrl + Shift + Enter keys together and drag the formula down.

=IF(B3="","",MAX(IF($B$3:B3=B3,$C$3:C3,""))+1)

[Glenn Kennedy]

Here's another possibility. However, you did not specify what would happen when a new job is added...

[shawnr72]

Glenn - Didn't think about the new jobs as we add these every day. Whenever we start a new job, we would need to start it at 1. I like what you did in the formula and wonder if adding the first is now possible.

[Glenn Kennedy]

Ignore my post at ~3. it was not good. This is much better. You do need an extra row (with a zero in it) to get this to work - but this does it all, I think... The extra row can be hidden, if you don't want to look at it...

[shawnr72]

Glenn - I have used formulas like this and trying to figure out exactly what it is doing so that when I apply to our document it will work. We will have more columns and not sure if the columns that I am showing will be next to each other.

Right after "LOOKUP" you have the (2,1..... What exactly is the lookup doing with these numbers? I see that it searches the previous Job numbers to find a match and then I assume if it is true it will search the PO # and then add one. Can you explain the Lookup part of the equation.

Thanks.

[Fotis1991]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)