Using Index/Match......NOT ARRAY!

[sssb2000]

hi all,
in A, i have this information:

Item 1
Item 1
Item 1
Item 2
Item 2
Item 6
Item 8
Item 8
Item 8
Item 8


In B1, i have a cell where i would input the name of the item (ie. Item 8)

I'd like column C to show all item 8s starting with C4 upto C8 in this case (because there's only 4 item 8s)

so, the list starting in C4 is dependent on the value of B1 and varries depending on the number of ItemX in column A.

I was wondering if someone could help me do this with a formula? (i can NOT use an ARRAY formula for what i'm doing for various restriction reasons)

 

[Richard Schollar]

Hi

Here's one way - formula in C1 is copied down as far as required.

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item 1</td><td style=";">Item 8</td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Item 1</td><td style="text-align: right;;"></td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Item 1</td><td style="text-align: right;;"></td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Item 2</td><td style="text-align: right;;"></td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Item 2</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Item 6</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">$A$1:$A1</font>)>COUNTIF(<font color="Red">$A:$A,$B$1</font>),"",$B$1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[sssb2000]

Hi

Here's one way - formula in C1 is copied down as far as required.

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item 1</td><td style=";">Item 8</td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Item 1</td><td style="text-align: right;;"></td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Item 1</td><td style="text-align: right;;"></td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Item 2</td><td style="text-align: right;;"></td><td style=";">Item 8</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Item 2</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Item 6</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Item 8</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">$A$1:$A1</font>)>COUNTIF(<font color="Red">$A:$A,$B$1</font>),"",$B$1</font>)</td></tr></tbody></table></td></tr></table><br />

thank you!
yes, that worked.

one other question....what if each "item" is attached to a name in column F? Ie. each item has a name attached to it. (so item 4 would have 4 names in the same row but in column F).

how do i capture that information in column C next to my listed items?

in other words, a formula would be used in C that would bring in the 'name' from column F for each of the 'items' that are now listed in column C.

 

[Richard Schollar]

I think you would need either array formulas or VBA for that I'm afraid

 

[RoryA]

Could you just clarify what your desired output now is?

 

[sssb2000]

Thanks everyone.
So, it turns out that i can't even use the ROW function that was suggested above.

I'm using Xcelsius (dashboarding tool) and it has some serious limitations as far as using the built in Excel.....no VBA, No Array, No ROW(), No Conditional formatting....ughhhh!

i have a table of items, each item followed by a series of parameters.

I will have a selector that will identify which item i want to look at and my ultimate goal is to just get the information for only that one item out and put it into a second table.

I've already got help accomplishing this using Arrays, but again, xcelsius won't allow useing it.

 

[taurean]

Can you sort your column A as I have done below? Then this might be of some help.

Excel Workbook
	A	B	C	D
1	Item 1	Item 8	Item 8, A	A
2	Item 1		Item 8, B	B
3	Item 1		Item 8, C	C
4	Item 2		Item 8, D	A
5	Item 2			B
6	Item 6			A
7	Item 8			A
8	Item 8			B
9	Item 8			C
10	Item 8			D
Sheet1

 

[mikerickson]

In C1 you could put the formula
=MATCH(B1,A:A,0)
and in C2 the formula.
=MATCH($B$1, OFFSET($A$1, C1, 0):$A$65536,0)+C1

Drag C2 down and you get all the row numbers that match the criteria. (Sorting column A is not needed)

Then =INDEX(F:F,C1,1) in D1 (dragged down) will get the result.

If the items in column F are unique, the formula in C2 could be changed to

=INDEX(F:F, MATCH(B1,A:A,0),1)

=INDEX(F:F, MATCH($B1, OFFSET($A$1, MATCH(C1,$F:$F,0),0):$A$6553, 0) + MATCH(C1,$F:$F,0),0),1)

 

[barry houdini]

Assuming you want just the names from column F this method doesn't need array entry and avoids ROW function.

In column D you need a list of integers starting with 1 in D1, 2 in D2 etc. all the way down as far as you have data in column A - for example purposes I assume only 10 rows - then use this formula in C1 copied down

=IF(D1>COUNTIF(A$1:A$10,B$1),"",INDEX(F$1:F$10,LARGE(INDEX((A$1:A$10=B$1)*(D$1:D$10),0),D1)))

It returns the names in reverse order of the way they appear.

If you can sort column A (or it already is sorted) as taurean suggests then that can be simplified to this

=IF(D1>COUNTIF(A$1:A$10,B$1),"",INDEX(F$1:F$10,MATCH(B$1,A$1:A$10,0)+D1-1))

to get the names in the correct order

 

[sssb2000]

In C1 you could put the formula
=MATCH(B1,A:A,0)
and in C2 the formula.
=MATCH($B$1, OFFSET($A$1, C1, 0):$A$65536,0)+C1

Drag C2 down and you get all the row numbers that match the criteria. (Sorting column A is not needed)

Then =INDEX(F:F,C1,1) in D1 (dragged down) will get the result.

If the items in column F are unique, the formula in C2 could be changed to

=INDEX(F:F, MATCH(B1,A:A,0),1)

=INDEX(F:F, MATCH($B1, OFFSET($A$1, MATCH(C1,$F:$F,0),0):$A$6553, 0) + MATCH(C1,$F:$F,0),0),1)

thank you!
it's functional and i've made it work with my spreadsheet.....

2 issues:

1) how can i get rid of all the #N/A? (i tried =if(isna(formula in C)),"", (formula in C)) and it didn't work.
2) i have to see if this works with Xcelsius now!