How to Find Multiple Results when Duplicate Variables are in Search Criteria using VLOOKUP in Excel 2010/Windows 7?

[dantepatrice]

Systems In Use
Excel – Microsoft Office Professional Plus 2010
Windows – Windows 7 Enterprise

Background Information:

· Comparing two sheets: Sheet #1 is the Form 15A and Sheet #2 is the Document Status Report.
· The current formula I’m using is =VLOOKUP(B12,'Document Status Report'! $A$2:$F$15,2,FALSE)
· The result I’m currently receiving are located in columns D (Standard Document Number) and E (Title), which is what I’m looking for, BUT because the LookUp Value must be based on column B (Supplier Document Category Code), I’m not receiving the results the way I would like them to populate.

The Results I Want
Although the search criteria is the same (e.g., BH, BI, CD) under the Supplier Document Category Code column, I would like Excel to not duplicate the value once it has been found. For instance, once the Standard Document Number 28967-999-V555-SOPP-00794 is allocated and assigned to a cell, I would like Excel to NOT use it again. Essentially, I’d like Excel to find the next number based on the same Supplier Document Category Code.

I’m retrieving data from the Document Status Report to incorporate into worksheet Form 15A. See sample worksheets below. Thanks for your help.

Form 15A Worksheet

[TABLE="width: 793"]
<tbody>[TR]
[TD]DOCUMENT DESCRIPTION NO.[/TD]
[TD]SUPPLIER DOCUMENT CATEGORY CODE[/TD]
[TD]DOCUMENT TITLE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STANDARD DOCUMENT NUMBER[/TD]
[TD]TITLE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD]SCHEMATICS[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]B01[/TD]
[TD]BH[/TD]
[TD]Single Line Diagrams[/TD]
[TD]28967-999-V555-SOPP-00794[/TD]
[TD]Electrical - Slab Heating System[/TD]
[/TR]
[TR]
[TD]B02[/TD]
[TD]BH[/TD]
[TD]Electrical Schematic Diagrams[/TD]
[TD]28967-999-V555-SOPP-00794[/TD]
[TD]Electrical - Slab Heating System[/TD]
[/TR]
[TR]
[TD]B03[/TD]
[TD]BI[/TD]
[TD]Instrument Wiring Termination Drawings[/TD]
[TD]28967-999-V555-SOPP-00796[/TD]
[TD]Electrical Lighting and Equipment - Main Platform[/TD]
[/TR]
[TR]
[TD]B04[/TD]
[TD]BI[/TD]
[TD]Electrical Wiring Diagrams including Connection Diagrams for Junction Boxes[/TD]
[TD]28967-999-V555-SOPP-00796[/TD]
[TD]Electrical Lighting and Equipment - Main Platform[/TD]
[/TR]
[TR]
[TD]B05[/TD]
[TD]BI[/TD]
[TD]Electrical Cable Ladder Layout[/TD]
[TD]28967-999-V555-SOPP-00796[/TD]
[TD]Electrical Lighting and Equipment - Main Platform[/TD]
[/TR]
[TR]
[TD]B06[/TD]
[TD]BI[/TD]
[TD]Electrical Lighting and Socket Outlet Layout and Wiring Diagrams[/TD]
[TD]28967-999-V555-SOPP-00796[/TD]
[TD]Electrical Lighting and Equipment - Main Platform[/TD]
[/TR]
[TR]
[TD]B07[/TD]
[TD]BN[/TD]
[TD]Loop Diagrams[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[TD]CIVIL[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Col[/TD]
[TD]CA[/TD]
[TD]Site Excavations / Backfill Plan[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]CO2[/TD]
[TD]CD[/TD]
[TD]Piling Plan[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]CO3[/TD]
[TD]CD[/TD]
[TD]Piling Design Details[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]C04[/TD]
[TD]CD[/TD]
[TD]Foundation Engineering Plan, Including Design Concept[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

Document Status Report Worksheet
[TABLE="width: 926"]
<tbody>[TR]
[TD]DCC[/TD]
[TD]SDN[/TD]
[TD]CDN[/TD]
[TD]KW[/TD]
[TD]DTD[/TD]
[TD]Title[/TD]
[/TR]
[TR]
[TD]BH[/TD]
[TD]28967-999-V555-SOPP-00794[/TD]
[TD]WS9-33333[/TD]
[TD]DP-440[/TD]
[TD]SD/VD[/TD]
[TD]Electrical - Slab Heating System[/TD]
[/TR]
[TR]
[TD]BH[/TD]
[TD]28967-999-V555-SOPP-00795[/TD]
[TD]WS9-33334[/TD]
[TD]DP-441[/TD]
[TD]SD/VD[/TD]
[TD]Schematic and Connection Diagrams[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00796[/TD]
[TD]WS9-33335[/TD]
[TD]DP-442[/TD]
[TD]SD/VD[/TD]
[TD]Electrical Lighting and Equipment - Main Platform[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00797[/TD]
[TD]WS9-33336[/TD]
[TD]DP-443[/TD]
[TD]SD/VD[/TD]
[TD]Electrical Lighting and Equipment - Roof[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00798[/TD]
[TD]WS9-33337[/TD]
[TD]DP-444[/TD]
[TD]SD/VD[/TD]
[TD]Lighting and Power Distribution Diagram[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00799[/TD]
[TD]WS9-33338[/TD]
[TD]DP-445[/TD]
[TD]SD/VD[/TD]
[TD]Cable Routing - Layout - Main Platform[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00800[/TD]
[TD]WS9-33339[/TD]
[TD]DP-446[/TD]
[TD]SD/VD[/TD]
[TD]Cable Routing - Layout - Roof A[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00801[/TD]
[TD]WS9-33340[/TD]
[TD]DP-447[/TD]
[TD]SD/VD[/TD]
[TD]Cable Routing - Layout - Roof B[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00802[/TD]
[TD]WS9-33341[/TD]
[TD]DP-448[/TD]
[TD]SD/VD[/TD]
[TD]Cable Routing - Layout - Roof C[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00803[/TD]
[TD]WS9-33342[/TD]
[TD]DP-449[/TD]
[TD]SD/VD[/TD]
[TD]Cable Routing - Layout - Roof D[/TD]
[/TR]
[TR]
[TD]BI[/TD]
[TD]28967-999-V555-SOPP-00804[/TD]
[TD]WS9-33343[/TD]
[TD]DP-450[/TD]
[TD]SD/VD[/TD]
[TD]Cable Routing - Layout - Roof E[/TD]
[/TR]
</tbody>[/TABLE]

If VLOOKUP isn’t the best option, please provide an alternative solution.

Thank you.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

You need an array formula to retrieve the SDNs.

Assuming you are entering the first formula in D2 try this

Array formula in D2 copied down
=IF(COUNTIF('Document Status Report'!$A:$A,B2),INDEX('Document Status Report'!$B:$B,SMALL(IF('Document Status Report'!$A$2:$A$500=$B2,ROW('Document Status Report'!$A$2:$A$500)),COUNTIF($B$2:$B2,$B2))),"")

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter


Then you can use a regular formula in F2 copied down
=IF(D2<>"",VLOOKUP(D2,'Document Status Report'!$B:$F,5,0),"")

confirmed with just Enter

Hope this helps

M.

 

[dantepatrice]

Marcelo,

I'm not having any luck with the formula. Do you mind defining each syntax?

Thanks.