vlookup multiple values and return values

**AMFISH** · 05-08-2012, 10:03 AM

I have a spreadsheet that contains the prescription number and date of service, I need to look up those two values on a separate sheet and when they both match return additional detail from that sheet. I have attached an example. Any help would be appreciated.
Thank you.

**amotto11** · 05-08-2012, 10:26 AM

On your example spreadsheet none of your Rx#'s match from one spreadsheet to another. if they had you can create an identifier in both spreadsheets between the Rx#'s and the prescription date, all you have to do is in a new column C1 enter (=A1&B1) and copy that formula down for both spreadsheets then use the formula =IF(ISNA(VLOOKUP(C2,'Prescription Log'!$C$2:$H$15,4,FALSE)),"",VLOOKUP(C2,'Prescription Log'!$C$2:$H$15,4,FALSE)) in D1 and drag to H1, then change the two 4's in the formula to correspond to the value that you are looking up. I noticed in your second spread sheet there were times in your dates, and in the first sheet there were no times. you would need to delete the times or get only the dates using the Date formula with left and right to get only the date otherwise your C column will not match up from page to page.

**Cutter** · 05-08-2012, 10:56 AM

@amotto11
Your formula could be simplified as:
=IFERROR(VLOOKUP(C2,'Prescription Log'!$C$2:$H$15,4,FALSE),"")

Actually, after looking at the sample file, you don't need to create a combined value on each sheet. You can just use a combined value on the Prescription Log sheet. I would suggest inserting a new column at the far left and in that column use this formula copied down:
=B2&INT(C2) - this gets rid of the time values and makes the combination for the VLOOKUP()

Now on the other sheet in cell C2 (and copied down) you can use:
=IFERROR(VLOOKUP($A2&$B2,'Prescription Log'!$A:$H,4,0),"")

Copy this formula over but change the number 4 reference as amotto11 already stated.