Using VLOOKUP to assign account numbers

[R3Billing]

I'm new to this forum, and hoping someone can help me use Excel more efficiently at work. Thanks in advance for any help you guys might be able to provide!

Background - I handle all the billing for a small ambulance company. Until I can learn how to create and maintain a database, I'm using a spreadsheet to keep up with our transports, claims status, etc. Each transport is added to the spreadsheet in a new row, with an account number in column B that is unique for each individual patient. When we transport a patient that we've never transported before, I simply find the highest account number and add 1 to it, creating a new account number. However, when we haul someone that we've already transported in the past, I have to find one of the previous entries for that patient in the spreadsheet in order to look up the patient's account number so that I can fill it in.

Objective - What I'm trying to do is figure out a formula that utilizes VLOOKUP to auto-fill the account number (column B) for patients whose social security number is already in the SSN column (column M), but it would need to create a new account number for new patients whose SSN isn't already in that column. The following is the formula I've tried using thus far (for the account number cell in column B, row 81):
=IF(VLOOKUP(M81,B2:M80,2,0)>0,VLOOKUP(M81,B2:M80,2,0),MAX(B2,B80)+1) Obviously, I'm doing something wrong, because the result is "#N/A".

If anyone can tell me what I'm doing wrong, or a better formula to use, I'd really appreciate it!

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=IF(COUNTIF(M2:M80,M81),INDEX(B2:B80,MATCH(M81,M2:M80,FALSE)),MAX(B2,B80)+1)

 

[Dryver14]

This may do it, I have extended the range of the lookup as I assume it grows with every new account

=IFERROR(VLOOKUP(M81,$B$2:$M$300,13),MAX(B:B)+1)

 

[R3Billing]

That formula returned a value, but not the correct one. I'll try playing with it to see if an adjustment will get it to work. Thanks!

Edit: I reentered Mr. Poulsom's response (correctly this time), and it worked! Thanks very much!!

 

[Dryver14]

ADVERTISEMENT

=IFERROR(VLOOKUP(M81,$B$2:$M$300,12),MAX(B:B)+1)

looking up the wrong column previously

 

[R3Billing]

Thank you both for your assistance! This will make data entry SO much easier for me. I can't believe how fast you guys responded, this site is awesome!

 

[Dryver14]

Your welcome,

With Andrews formula, if you are going to drag it down the columns you need to lock it from the top thus:

=IF(COUNTIF(M$2:M80,M81),INDEX(B$2:B80,MATCH(M81,M$2:M80,FALSE)),MAX(B$2,B80)+1)

 

[R3Billing]

Yeah, I figured that out pretty quickly, lol. I'm also using portions of that formula to auto-fill first and last names for patients that are already in the spreadsheet, saving me even more time. I'm very excited that data entry will be somewhat less tedious thanks to the help you guys have provided!