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

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

<tbody>
</tbody>

Document Status Report Worksheet

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

<tbody>
</tbody>

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.