Home

Vlookup 2 worksheeets 1 criteria multiple up to 3 matches for a unique identifier

%3CLINGO-SUB%20id%3D%22lingo-sub-823650%22%20slang%3D%22en-US%22%3EVlookup%202%20worksheeets%201%20criteria%20multiple%20up%20to%203%20matches%20for%20a%20unique%20identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823650%22%20slang%3D%22en-US%22%3ESingle%20workbook%20worksheet%201%20is%20all%20Ed%20visits%20during%20a%20specified%20time%20Worksheet%202%20is%20all%20antibiotics%20prescribed%20during%20the%20same%20time%20period%20up%20to%203%20antibiotics%20Rx'd%20in%20a%20visit.%20Formula%20is%3A%20%7B%3DIFERROR(INDEX(Table1%5BMedication%5D%2CSMALL(IF(%24A8%3DTable1%5BKey%5D%2CROW(Table1%5BMedication%5D)-2%2C%22%22)%2C%20COLUMN()-18))%2C%22%22)%7D%20Matching%20Antibiotics%20prescribed%20to%20a%20particular%20ED%20visit%20Table1%5BMedication%5D%20is%20found%20on%20the%20worksheet%20that%20lists%20all%20antibiotics%20prescribed%20Total%20ED%20visits%20~2500%20Total%20ABX%20prescribed%20722%20(some%20patient%20more%20than%20one%20in%20a%20visit.%20Each%20ABX%20on%20a%20separate%20row.%20Created%20a%20common%2C%20unique%20identifier%20in%20column%20A%20on%20both%20worksheets%20Only%20getting%20empty%20cells%20(finding%20no%20matches)%20Syntax%20error%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-823650%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823673%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%202%20worksheeets%201%20criteria%20multiple%20up%20to%203%20matches%20for%20a%20unique%20identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393200%22%20target%3D%22_blank%22%3E%40zond2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20looks%20correct%20from%20syntax%20point%20of%20view.%20Perhaps%20incorrect%20using%20of%20ROW()%2C%20COLUMN()%20-%20hard%20to%20say%20without%20the%20file.%3C%2FP%3E%0A%3CP%3EIn%20any%20case%20it's%20better%20to%20use%20variables%20in%20calculations%2C%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EROW(Table1%5BMedication%5D)-ROW(Table1%5B%5B%23Headers%5D%2C%5BKey%5D%5D)%0A%0Ainstead%20of%0AROW(Table1%5BMedication%5D)-3%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
zond2
New Contributor
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

@zond2 

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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies