Vlookup query

[midframe]

Hi, just joined. Been using excel on and off for years with gratifying success. However, I seem have been stumped.
i run a small spreadsheet to roster a group of drivers to deliver people to outpatient treatment. On most occasions the driver will deliver the patient/s and wait to return them home. However, sometimes it is necessary for a patient to be delivered at one time and picked up much later by another driver perhaps.

I am using vlookup formulas for attracting patient details -address, contact phone, hospital, department and appointment time - from two different data sources. All this works fine because my vlookup_value is a concatenation of the day number and patient name particularly for tasking bookings and provides a unique record on the day and using the FALSE setting for the return values in each relevant cell . Now the problem...

By entering the patient again on the same day for a different action, eg "Return only", the net result for the day is dependent upon which order the lookup value appears in the list; no doubt to be expected. Is there a way of getting two different sets of information on the same day for the same patient using lookup?

I hope the question is explained enough for someone to perhaps help. I would like to retain the relative simplicity of vlookups rather than redeveloping the whole project in VBA if at all possible.

Thanking whomever in anticipation,

midframe

 

[Pup Denab]

HI midframe
Welcome to the forum
It looks like you only need to identify, if a patient has 2 trips on the same day, as below, (but you will probably need the date also)
in this example patient b has 1 and 2 assigned in different rows, the second table will shold the formula used to identify those

[TABLE="width: 161"]
<tbody>[TR]
[TD]Patient[/TD]
[TD]Trip id 1 or 2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]i[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]j[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Patient
a	=COUNTIF($A$1:A2,A2)
b	=COUNTIF($A$1:A3,A3)
c	=COUNTIF($A$1:A4,A4)
d	=COUNTIF($A$1:A5,A5)
e	=COUNTIF($A$1:A6,A6)
f	=COUNTIF($A$1:A7,A7)
g	=COUNTIF($A$1:A8,A8)
h	=COUNTIF($A$1:A9,A9)
i	=COUNTIF($A$1:A10,A10)
j	=COUNTIF($A$1:A11,A11)
b	=COUNTIF($A$1:A12,A12)

<tbody>
[TD="width: 186"]Trip id 1 or 2[/TD]

</tbody>

Hope this helps
Regards
Pup

 

[midframe]

Thanks Pup, Your countif got me thinking and the mention of the date in your suggestion had me add an extra character to the end of the name and put that in a drop down for selection. Seems to work fine. I "trap" the extra character to allow the formula to retain the integrity of the result for viewing. midframe