INDEX MATCH MATCH quesiton

[Kurt]

I am trying to get the values of the New Tag Number, Size Range, and Revised Short Description to display when the PIPE SPEC number is selected.

NEW TAG NUMBER SIZE RANGE REVISED SHORT DESCRIPTION PIPE SPEC
V-T10001 1/2-2 CL 125, BRASS, THR'D, BOLTED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10000 1/2-2 CL 125, DI, THR'D, BOLTED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10001 1/2-2 CL 125, DI, THR'D, BOLETED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10203 2-16 CL 150, DI, FF, BOLTED BONNET, AWWA C509/C515, FM APPROVED, NSF61 CERTIFIED 1PD0P1
V-A10203 2-16 CL 150, DI, FF, BOLTED BONNET, AWWA C509/C515, FM APPROVED, NSF61 CERTIFIED 1CS1D1
V-A10203 2-16 CL 150, DI, FF, BOLTED BONNET, AWWA C509/C515, FM APPROVED, NSF61 CERTIFIED 1PD0P2
V-A10301 1/2-2 CL 125, DI, FF, BOLTED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10304 1/2-2 CL 125, DI, FF, BOLTED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10302 3-12 CL 125, BRASS, FF, BOLTED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10305 3-12 CL 125, DI, FF, BOLTED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10306 3-12 CL 125, DI, FF, BOLTED BONNET, AWWA C500, NSF61 CERTIFIED 1CG1U5
V-A10303 3-12 CL 125, DI, FF AWWA C515, POST INDICATOR VALVE, FM APPROVED, NSF61 CERTIFIED 1CG1U1
V-A10303 3-12 CL 125, DI, FF AWWA C515, POST INDICATOR VALVE, FM APPROVED, NSF61 CERTIFIED 1PD0P1
V-A10303 3-12 CL 125, DI, FF AWWA C515, POST INDICATOR VALVE, FM APPROVED, NSF61 CERTIFIED 1PV0W1
V-B10200 2-24 CL 150, ALLOY B, RF, BOLTED BONNET, API 603 1NH0G1
V-B10202 1/2-1 1/2 CL 150, ALLOY B, RF, BOLTED BONNET, API 603 1NH0G1
V-B10201 2-24 CL 150, ALLOY B, RF, BOLTED BONNET, API 603, LOW E 1SP9P1
V-B10203 1/2-1 1/2 CL 150, ALLOY B, RF, BOLTED BONNET, API 603, LOW E 1SP9P1
V-C00100 1/2-1 1/2 CL 1500, A105, SW, WELDED BONNET, API 602, LOW E 9CS1P1
V-C00100 1/2-1 1/2 CL 1500, A105, SW, WELDED BONNET, API 602, LOW E 9CS1P2

I have named this ValveTable and it starts in column A. Of course the actual table is a lot larger but I shortened it.

How do I get it to display these items using iNDEX(MATCH, MATCH) or is there a better way?

For this example we can display these starting in column H.

Is there a link for Excel Jeanie in English? Every time I click on a link in the forum it appears to be in German.

 

[AhoyNC]

English link for Excel Jeanie.
http://www.excel-jeanie-html.de/html/allg_en.php

 

[Kurt]

I tried installing it even after I closed Excel. I looked in Add Ins and I don't see a thing.

 

[Aladin Akyurek]

Care to post a PIPE SPEC number for which you want a sublist?

 

[Kurt]

Sure let's try the first one. 1CG1U5

 

[Aladin Akyurek]

Sure let's try the first one. 1CG1U5

Does this occur stand-alone in the data or as part of something else like: NSF61 CERTIFIED 1CG1U5?

 

[Kurt]

Does this occur stand-alone in the data or as part of something else like: NSF61 CERTIFIED 1CG1U5?

This is part of the Description.

 

[Aladin Akyurek]

This is part of the Description.

Let Sheet1 house the data.

Let A1 of Sheet2 house 1CG1U5, the search value of interest.

In A2 just enter:

=COUNTIFS(PipeSpec,A1)

In A3:D3 enter the following: Idx, New Tag Number, Size Range, and Revised Short Description

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(ISNUMBER(SEARCH($A$1,PipeSpec)),ROW(ValveTable)-ROW(INDEX(ValveTable,1,1))+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(ValveTable,$A4,MATCH(B$3,OFFSET(ValveTable,-1,0,1),0)))

 

[Kurt]

Let Sheet1 house the data.

Let A1 of Sheet2 house 1CG1U5, the search value of interest.

In A2 just enter:

=COUNTIFS(PipeSpec,A1)

In A3:D3 enter the following: Idx, New Tag Number, Size Range, and Revised Short Description

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(ISNUMBER(SEARCH($A$1,PipeSpec)),ROW(ValveTable)-ROW(INDEX(ValveTable,1,1))+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(ValveTable,$A4,MATCH(B$3,OFFSET(ValveTable,-1,0,1),0)))

When I try to enter your formula in B4 I get a #REF error.

What should I check for now?

Thank you for your efforts so far.

 

[Aladin Akyurek]

When I try to enter your formula in B4 I get a #REF error.

What should I check for now?

Thank you for your efforts so far.

Did you follow the instructions literally?

If you did, care to post the exact ranges for ValveTable and PipeSpec?