Reverse lookup returning multiple column headers to a single cell

[Paperless]

Hi everyone,

I am trying to do a reverse lookup to return column headers by matching the data items within a grid list.

Currently, I am facing a difficulty in returning multiple column headers (Sheet 1) and they should be returned to a single cell in the "Interface Sheet".

Interface Sheet Example:

___A____| _____B
1 Query__|_Query data field
2 TEST01 | Customer ID, Gender, Address, Age
3 TEST02 | Customer ID, Gender, Martial Status
4 TEST03 | Customer ID, Phone Number, Age
5 TEST04 | Gender, Address, Age

Sheet 1 Example:

____A___________B___________C___________D__________E___________F__________G
1 __S/N______Customer ID____Gender___Phone Number__Address____Marital Status____Age
2 TEST01-1_____TEST01______TEST01_________________TEST01__________________TEST01
3 TEST02-1_____TEST02______TEST02_____________________________TEST02
4 TEST03-1_____TEST03_________________TEST03______________________________TEST03
5 TEST04-1_________________TEST04_________________TEST04__________________TEST04

In Interface Sheet, cells B2 to B5 contains the lookup formula to return column headers from Sheet 1 as expressed in the examples. To make it simple, under query "TEST01" in the Interface Sheet, cell B2 should be able to return all the column headers from Sheet 1 that contains "TEST01" in each respective column and fit them all in cell B2.

Any lookup formulas are fine with me as long as it can solve the problem!

 

[Aladin Akyurek]

In B2 of Interface control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF(INDEX(Sheet1!$B$2:$G$5,MATCH(A2&"*",Sheet1!$A$2:$A$5,0),0)<>"",Sheet1!$B$1:$G$1,""))

If you don't have TEXTJOIN on your system, we can use ACONCAT, a user-defined function.

 

[Paperless]

Unfortunately, my system does not consist of TEXTJOIN in its formula as it is the 2010 version.

Is there an alternative besides ACONCAT in solving this? I would like to keep it VBA free.

 

[Aladin Akyurek]

Unfortunately, my system does not consist of TEXTJOIN in its formula as it is the 2010 version.

Is there an alternative besides ACONCAT in solving this? I would like to keep it VBA free.

Not that I know of, but I guess you are calling for others chime in.

 

[Mike Szczesny]

Can you use something like this? This would be a 2 step process. I essentially mirrored your example. Assume your sheet1 range is A1:G8. I entered a criteria (Test01) in I1. I placed my first formula in J1. I used the =iferror(.....)," ") to clear error messages after you get all your results. Then you would select the actual result range. In J1 I used =[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($B$1:$G$1,SMALL(IF($B$2:$G$5=$I$1,COLUMN($B$2:$G$5)-COLUMN($B$2)+1),ROWS($J$1:J1)))," ") You need to use Cntrl+Shift+Enter. Copy down. The results for Test01 is four items. So you would use the range J1:J4 for the second formula.

I put the second formula in K1. The formula is =concatenate(transpose(J1:J4&",")) Before you hit enter, highlight the transpose(.....) with your mouse. Hit F9 and remove the { and }. Then enter. Hope this helps[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Szczesny]

Sorry. forgot to send your my data example.

[TABLE="width: 905"]
<colgroup><col><col><col><col><col span="7"><col></colgroup><tbody>[TR]
[TD]No[/TD]
[TD]ID[/TD]
[TD]Gender[/TD]
[TD]Phone[/TD]
[TD]Address[/TD]
[TD]Status[/TD]
[TD]Age[/TD]
[TD][/TD]
[TD]Test01[/TD]
[TD] [/TD]
[TD]Test01[/TD]
[TD]ID,Gender,Address,Age,[/TD]
[/TR]
[TR]
[TD]Test01-1[/TD]
[TD]Test01[/TD]
[TD]Test01[/TD]
[TD][/TD]
[TD]Test01[/TD]
[TD][/TD]
[TD]Test01[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gender[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test02-1[/TD]
[TD]Test02[/TD]
[TD]Test02[/TD]
[TD][/TD]
[TD][/TD]
[TD]Test02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test03-1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Test03[/TD]
[TD][/TD]
[TD][/TD]
[TD]Test03[/TD]
[TD][/TD]
[TD][/TD]
[TD]Age[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test07-1[/TD]
[TD][/TD]
[TD]Test04[/TD]
[TD][/TD]
[TD]Test04[/TD]
[TD][/TD]
[TD]Test04[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Paperless]

Can you use something like this? This would be a 2 step process. I essentially mirrored your example. Assume your sheet1 range is A1:G8. I entered a criteria (Test01) in I1. I placed my first formula in J1. I used the =iferror(.....)," ") to clear error messages after you get all your results. Then you would select the actual result range. In J1 I used =[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($B$1:$G$1,SMALL(IF($B$2:$G$5=$I$1,COLUMN($B$2:$G$5)-COLUMN($B$2)+1),ROWS($J$1:J1)))," ") You need to use Cntrl+Shift+Enter. Copy down. The results for Test01 is four items. So you would use the range J1:J4 for the second formula.

I put the second formula in K1. The formula is =concatenate(transpose(J1:J4&",")) Before you hit enter, highlight the transpose(.....) with your mouse. Hit F9 and remove the { and }. Then enter. Hope this helps[/TD]
[/TR]
</tbody>[/TABLE]

This is what I wanted but is it possible to customise the IFERROR(...) formula to be dragged across horizontally i.e. J1:M1 instead of vertically J1:J4?

The problem will be solved if it can be done. Thank you!

 

[Aladin Akyurek]

So you abandon the single cell goal... In that case:

In B2 of interface control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$B$1:$G$1,SMALL(IF(INDEX(Sheet1!$B$2:$G$5,MATCH($A2&"*",Sheet1!$A$2:$A$5,0),0)=$A2,COLUMN(Sheet1!$B$1:$G$1)-COLUMN(Sheet1!$B$1)+1),COLUMNS($B2:B2))),"")

 

[Paperless]

So you abandon the single cell goal... In that case:

In B2 of interface control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$B$1:$G$1,SMALL(IF(INDEX(Sheet1!$B$2:$G$5,MATCH($A2&"*",Sheet1!$A$2:$A$5,0),0)=$A2,COLUMN(Sheet1!$B$1:$G$1)-COLUMN(Sheet1!$B$1)+1),COLUMNS($B2:B2))),"")

It perfectly solve my problem. My apology if I did not articulate my idea well on the single cell. I figured out that I can drag the formulas across horizontally and use a manual function but still dynamic to combine the results into one cell. All these formulas will be contained inside a working sheet and become hidden, and use a lookup function to return it to the main sheet.

Nonetheless, thank you Aladin and Mike so much for the help!