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:

Code	Phase 1	Phase 2	Phase 3
aaa	2		3
bbb		3	4
ccc	1	4	1
ddd	2		2

<tbody>
</tbody>

What I Have Now (phase 1):

Code	Qty
aaa	2
	#N/A
ccc	1
ddd	2

<tbody>
</tbody>

The Result I Want:

Phase 1		Phase 2
Code	Quantity	Code	Quantity
aaa	2	bbb	3
ddd	1	ccc	4
eee	2

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Code	Phase 1	Phase 2	Phase 3		Phase 1	3​
2​	aaa	2​		3​		Code	Qty
3​	bbb		3​	4​		aaa	2​
4​	ccc	1​	4​	1​		ccc	1​
5​	ddd	2​		2​		ddd	2​
6​
7​

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.