# Using VLOOKUP to assign account numbers

#### R3Billing

##### New Member
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Andrew Poulsom

##### MrExcel MVP
Welcome to MrExcel.

Try:

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

#### Dryver14

##### Well-known Member
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

##### New Member
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:

#### Dryver14

##### Well-known Member
=IFERROR(VLOOKUP(M81,\$B\$2:\$M\$300,12),MAX(B:B)+1)

looking up the wrong column previously

#### R3Billing

##### New Member
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

##### Well-known Member

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

##### New Member
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!

Replies
3
Views
141
Replies
2
Views
200
Replies
1
Views
99
Replies
3
Views
218
Replies
6
Views
395

1,195,665
Messages
6,011,026
Members
441,579
Latest member
satishrazdhan

### 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.

### Which adblocker are you using?

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

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