Nested index match if countif statement

[VKat13]

Was wondering if someone out there could assist? I am building a sales data report (in Excel 2010) for our field team in which they can enter in an account number for one of their stores and the report spits out the top 6 selling items in order of sales rank.</SPAN>

I have the following formula which will return the heading (Sales Item) based on the rank:</SPAN>

=IF(COUNTIF(B2:G2,1),LOOKUP(2,1/(B2:G2=1),B1:G1),"")</SPAN>

I have tried combining this with an INDEX/MATCH statement to reference only the row relevant to the user inputted Acct # but it returns a #VALUE error. </SPAN>

My expected result and data is as per below:</SPAN>

Hopefully I make sense and hoping someone out there will be able to shed some light on how this can be done? </SPAN>

Greatly appreciate any assistance anyone can provide. </SPAN>

Thanks</SPAN>
Kate</SPAN></SPAN>

SALES TABLE<o></o>					<o></o>
<o></o>​	A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​	F<o></o>​	G<o></o>​		SAMPLE OUTPUT<o></o>
1<o></o>​	Acct.#<o></o>​	Banana<o></o>	Pineapple<o></o>	Apple<o></o>	Orange<o></o>	Peach <o></o>	Cherry<o></o>		Acct #<o></o>	123456<o></o>​
2<o></o>​	123456<o></o>​	750<o></o>​	223<o></o>​	124<o></o>​	653<o></o>​	121<o></o>​	353<o></o>​
3<o></o>​	234567<o></o>​	167<o></o>​	712<o></o>​	653<o></o>​	862<o></o>​	456<o></o>​	332<o></o>​		Sales Rank<o></o>​	Fruit Type<o></o>
4<o></o>​	345678<o></o>​	123<o></o>​	223<o></o>​	122<o></o>​	552<o></o>​	665<o></o>​	125<o></o>​		1<o></o>​	Banana<o></o>​
5<o></o>​	456789<o></o>​	653<o></o>​	356<o></o>​	233<o></o>​	421<o></o>​	352<o></o>​	147<o></o>​		2<o></o>​	Orange<o></o>​
6<o></o>​	567891<o></o>​	225<o></o>​	455<o></o>​	458<o></o>​	674<o></o>​	223<o></o>​	225<o></o>​		3<o></o>​	Cherry<o></o>​
7<o></o>​	678911<o></o>​	682<o></o>​	653<o></o>​	585<o></o>​	365<o></o>​	665<o></o>​	109<o></o>​		4<o></o>​	Pineapple<o></o>​
8<o></o>​	789112<o></o>​	885<o></o>​	128<o></o>​	721<o></o>​	428<o></o>​	158<o></o>​	289<o></o>​		5<o></o>​	Apple<o></o>​
									6<o></o>​	Peach<o></o>​
RANKING TABLE<o></o>
<o></o>	A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​	F<o></o>​	G<o></o>​
1<o></o>​	Acct.#<o></o>​	Banana<o></o>	Pineapple<o></o>	Apple<o></o>	Orange<o></o>	Peach <o></o>	Cherry<o></o>
2<o></o>​	123456<o></o>​	1<o></o>​	4<o></o>​	5<o></o>​	2<o></o>​	6<o></o>​	3<o></o>​
3<o></o>​	234567<o></o>​	6<o></o>​	2<o></o>​	3<o></o>​	1<o></o>​	4<o></o>​	5<o></o>​
4<o></o>​	345678<o></o>​	5<o></o>​	3<o></o>​	6<o></o>​	2<o></o>​	1<o></o>​	4<o></o>​
5<o></o>​	456789<o></o>​	1<o></o>​	3<o></o>​	5<o></o>​	2<o></o>​	4<o></o>​	6<o></o>​
6<o></o>​	567891<o></o>​	5<o></o>​	3<o></o>​	2<o></o>​	1<o></o>​	6<o></o>​	5<o></o>​
7<o></o>​	678911<o></o>​	1<o></o>​	3<o></o>​	4<o></o>​	5<o></o>​	2<o></o>​	6<o></o>​
8<o></o>​	789112<o></o>​	1<o></o>​	6<o></o>​	2<o></o>​	3<o></o>​	5<o></o>​	4<o></o>​

<TBODY>
</TBODY>

 

[markmzz]

Hi Kate,

Try this small modification in your formula:

Layout

Acct.#	Banana	Pineapple	Apple	Orange	Peach	Cherry	SALES TABLE	SAMPLE OUTPUT
123456	750	223	124	653	121	353		Acct #	123456
234567	167	712	653	862	456	332		Sales Rank	Fruit Type
345678	123	223	122	552	665	125		1	Banana
456789	653	356	233	421	352	147		2	Orange
567891	225	455	458	674	223	225		3	Cherry
678911	682	653	585	365	665	109		4	Pineapple
789112	885	128	721	428	158	289		5	Apple
								6	Peach
*
Acct.#	Banana	Pineapple	Apple	Orange	Peach	Cherry	RANKING TABLE
123456	1	4	5	2	6	3
234567	6	2	3	1	4	5
345678	5	3	6	2	1	4
456789	1	3	5	2	4	6
567891	5	3	2	1	6	5
678911	1	3	4	5	2	6
789112	1	6	2	3	5	4
*******	********	**********	*******	********	*******	*******	**************	***************	*********

<tbody>
</tbody>

Formula

In J4

=IF(COUNTIF(INDEX($B$12:$G$18,MATCH($J$2,$A$12:$A$18,0),),$I4),
LOOKUP(2,1/(INDEX($B$12:$G$18,MATCH($J$2,$A$12:$A$18,0),)=$I4),$B$11:$G$11),"")

Markmzz

 

[VKat13]

Perfect!
Thanks so much for taking the time to look at this Markmzz. Very much appreciated!

 

[markmzz]

Perfect!
Thanks so much for taking the time to look at this Markmzz. Very much appreciated!

You are welcome and thanks for the feedback.

Markmzz