Lookup, Index, match or something else?

[marksemple]

I'd like to create a table, the headings are pulled from data on another range on another sheet in one column. The content being the data corresponding to the heading titles from another column. The hardest part is trying to explain in enough detail what I'm after.

[JohnTopley]

It is much easier for all if you ....

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[marksemple]

I've uploaded a test file, however, I can't see / find it to attach?

[marksemple]

Thanks for your help. Here's the worksheet, hopefully there's enough information to explain?

[JohnTopley]

In A2 of Sheet2

=IFERROR(INDEX(Sheet1!$A$2:$A$8,SMALL(IF(Sheet1!$B$2:$B$8=Sheet2!A$1,ROW(Sheet1!$A$2:$A$8)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+shift+Enter

Copy across and down

[Pete_UK]

Put this formula in C2 of Sheet1:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

and copy down at least to the bottom of your data (it doesn't matter if you copy further - you will get a hyphen to show that the formula is active).

Then you can put this formula in A2 of Sheet2:

=IFERROR(INDEX(Sheet1!$A:$A,MATCH(A$1&"_"&ROWS($1:1),Sheet1!$C:$C,0)),"")

Copy this across into B2:C2, then copy the formulae in A2:C2 down as far as you need to.

Hope this helps.

Pete

[Tony Valko]

Try this...

Data Range

	A	B	C
1	Parts	Stationery	Cleaning
2	Nuts	Paper	Cloths
3	Bolts	Pens	Mops
4			Sprays
5

This array formula** entered in A2:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$2:$B$8=A$1,ROW(Sheet1!$B$2:$B$8)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to C2 then down until you get a row full of blanks.

[marksemple]

Thank you!

That's very helpful.
What if there were columns in-between description and department?
Blanks show as 0, can anything be done to ignore the blanks, and bring everything up, would still need to be dynamic.

Thanks again.

[Tony Valko]

Post an updated file to show us what you mean.