Also try adding FALSE to both VLOOKUPs

=VLOOKUP(A1,DoctorInfo,2,FALSE)

Judith
--
Hope this helps


"JudithJubilee" wrote:

Hello rkstaggers,

Can you copy and paste the formula you have in cell E1 (the first VLOOKUP)
into a message and I'll have a look.

Judith

"rkstaggers" wrote:

I changed them to look at only A:1 to A:5 and same for colum B:1 toB:5. It
still brings up the wrong address and miles. I will move the info to another
sheet, but need to get this fixed first. D:1 works with all five Dr's names.

"JudithJubilee" wrote:

Hello rkstaggers,

I hope Dave doesn't mind me jumping in but this caught my eye!

When you copy the formula down the columns it is changing the cell references
that it is refering to. Each cell in columns D and E need to look at the same
table of Doctor's information. As Dave said he was showing you how the
formula worked to start you off - you just need to tweak where you are
putting data.

You should really have the table of info somewhere else on the worksheet. Cut
and Paste the Doctor table (A1:C5) and put it either on a different worksheet
or at the top right of the sheet you are on. Then you can use column A to
enter the Dr names, B for the address and C for the mileage.

For the formula to work you want all the cells in B and C to refer to the
same set of cells. The best way to do this is to give the table cells a Range
Name. Highlight the cells you have cut and pasted from A1:C5 and press Ctrl +
F3. Give your table a name, eg. DoctorInfo, and click OK. (No spaces are
allowed in the name).

Now in Column B use the formula Dave gave you but with the Range name instead
of refs:

=VLOOKUP(A1,DoctorInfo,2)

In C

=VLOOKUP(A1,DoctorInfo,3)

You will now be able to copy this down.

Post back if you have any problems.

Judith


rkstaggers wrote:
Dave,
Ok it worked on line 1. I filled down E:1 and F:1 to continue my log.
However when I put Dr's name in colum D, rows 2, 3, 4 etc, I get incorect
address and mileage. The mileage for that address and are correct, but not
correct for the Dr's name.

After looking around it started to come back. I used Microsoft Works, but not
for many years. But I was using the IF in an formula and was going to be shut
[quoted text clipped - 39 lines]
Let me know if you cannot get this to work for your requirements
:(