Forum Discussion
Vlookup 2 worksheeets 1 criteria multiple up to 3 matches for a unique identifier
Single workbook worksheet 1 is all Ed visits during a specified time Worksheet 2 is all antibiotics prescribed during the same time period up to 3 antibiotics Rx'd in a visit. Formula is: {=IFERROR(INDEX(Table1[Medication],SMALL(IF($A8=Table1[Key],ROW(Table1[Medication])-2,""), COLUMN()-18)),"")} Matching Antibiotics prescribed to a particular ED visit Table1[Medication] is found on the worksheet that lists all antibiotics prescribed Total ED visits ~2500 Total ABX prescribed 722 (some patient more than one in a visit. Each ABX on a separate row. Created a common, unique identifier in column A on both worksheets Only getting empty cells (finding no matches) Syntax error?
1 Reply
Formula looks correct from syntax point of view. Perhaps incorrect using of ROW(), COLUMN() - hard to say without the file.
In any case it's better to use variables in calculations, like
ROW(Table1[Medication])-ROW(Table1[[#Headers],[Key]]) instead of ROW(Table1[Medication])-3
