VLOOKUP or INDEX/MATCH Result Skips Blanks Help

[bauer_excel]

Please I really need help! I am definitely a beginning excel user, and after using google and looking through this forum I am still unable to figure this out. Answers will be much appreciated!

Basically I have data that specifies how much of each item (codes) are used per phase. For example, 2x aaa is used in phase 1 and 3x aaa used in phase 3, but none used in phase 2.

I want to return result that have phases as the column header, detailing underneath what codes/items are used and how much is used. So for Phase 1, since bbb is not used, it is not listed. What I hope is to be able to automatically generate the result from the data itself, so something like a formula for each column of the result--as simple as possible so the formula is easy to understand (some things can be manually inputted, such as names of phases).

Currently I am using a combo of IF/INDEX/MATCH formula to return the results I want, but I am unable to skip the blanks (so for phase 1 I am getting aaa blankspace ccc ddd even though I only want aaa ddd eee).

This is my first time using this forum, I hope I explained it well enough. Please teach me how to best do this efficiently, thanks in advance


The Data:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Code[/TD]
[TD="align: center"]Phase 1[/TD]
[TD="align: center"]Phase 2[/TD]
[TD="align: center"]Phase 3[/TD]
[/TR]
[TR]
[TD="align: center"]aaa[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]bbb[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]ccc[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]ddd[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]









What I Have Now (phase 1):

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Code[/TD]
[TD="align: center"]Qty[/TD]
[/TR]
[TR]
[TD="align: center"]aaa[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]ccc[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]ddd[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]


The Result I Want:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Phase 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Phase 2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Code[/TD]
[TD="align: center"]Quantity[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Quantity[/TD]
[/TR]
[TR]
[TD="align: center"]aaa[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]bbb[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]ddd[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]ccc[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]eee[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Code[/td][td]Phase 1[/td][td]Phase 2[/td][td]Phase 3[/td][td][/td][td]Phase 1[/td][td]

3​

[/td][/tr]

[tr][td]

2​

[/td][td]aaa[/td][td]

2​

[/td][td][/td][td]

3​

[/td][td][/td][td]Code[/td][td]Qty[/td][/tr]

[tr][td]

3​

[/td][td]bbb[/td][td][/td][td]

3​

[/td][td]

4​

[/td][td][/td][td]aaa[/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]ccc[/td][td]

1​

[/td][td]

4​

[/td][td]

1​

[/td][td][/td][td]ccc[/td][td]

1​

[/td][/tr]

[tr][td]

5​

[/td][td]ddd[/td][td]

2​

[/td][td][/td][td]

2​

[/td][td][/td][td]ddd[/td][td]

2​

[/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

G1, just enter:

=COUNT(INDEX($B$2:$D$5,0,MATCH(F1,$B$1:$D$1,0)))

F3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$3:F3)<=$G$1,
    INDEX($A$2:$A$5,SMALL(IF(ISNUMBER(INDEX($B$2:$D$5,0,MATCH($F$1,$B$1:$D$1,0))),
    ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$3:F3))),"")

G3, just enter and copy down:

=IF($F3="","",VLOOKUP($F3,$A$2:$D$5,MATCH($F$1,$A$1:$D$1,0),0))

The same set up for the other "phases".

 

[bauer_excel]

Thanks for your help, I will test it out!

However, as I need to apply this rule to a whole bunch of other things (like a huge excel), could you run through the rationale briefly so I can replicate the method?

 

[Aladin Akyurek]

Thanks for your help, I will test it out!

However, as I need to apply this rule to a whole bunch of other things (like a huge excel), could you run through the rationale briefly so I can replicate the method?

G1: Dtermine (count) the number of numeric records in the Phase 1 range.
F3: Retieve each code associated with a numeric record from the Phase 1 range.
G3: Look up the quatity assoaciated with each code.

 

[bauer_excel]

Thanks again. Honestly I wanted to respond earlier but this is the first time I'm looking at array formulas so I am just taking time to learn how to use it.

Why have ROW($A$2:$A$5)-ROW($A$2)+1 in the 2nd formula? With the dollar sign doesn't this always return a constant number? Oh man I apologize for my lack of excel understanding.

G1: Dtermine (count) the number of numeric records in the Phase 1 range.
F3: Retieve each code associated with a numeric record from the Phase 1 range.
G3: Look up the quatity assoaciated with each code.

 

[Aladin Akyurek]

Thanks again. Honestly I wanted to respond earlier but this is the first time I'm looking at array formulas so I am just taking time to learn how to use it.

You are welcome.

Why have ROW($A$2:$A$5)-ROW($A$2)+1 in the 2nd formula? With the dollar sign doesn't this always return a constant number? Oh man I apologize for my lack of excel understanding.

ROW($A$2:$A$5)-ROW($A$2)+1 creates an integer vector matching the length of the target range: {1;2;3;4}. The positief results a re mapped onto this as integers and negative results as FALSE values. Such mapping is then used by the SMALL function.