Using VLOOKUP to assign account numbers

R3Billing

New Member
Joined
Apr 6, 2013
Messages
4
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)
 
Upvote 0
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!!
 
Last edited:
Upvote 0
=IFERROR(VLOOKUP(M81,$B$2:$M$300,12),MAX(B:B)+1)

looking up the wrong column previously
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top