2

I have a table of information by columns. I have values to look up within the table but I am unable to get the column heading as a result.

Tried both LOOKUP, Match and Index. Not sure what I want can be done since the lookup is throughout the entire table.

enter image description here

rmom4us
  • 21
  • 1
  • 1
    Please put the input data in [Table Markdown](https://www.tablesgenerator.com/markdown_tables#) and provide the expected output for your input data. Thanks – David Leal Jul 08 '23 at 21:17
  • 2
    Does this answer your question? [Find Column Header based on criteria from an array](https://stackoverflow.com/questions/76359879/find-column-header-based-on-criteria-from-an-array) – Mayukh Bhattacharya Jul 08 '23 at 21:26
  • 1
    Please do some research the community all these queries are solved priorly. – Mayukh Bhattacharya Jul 08 '23 at 21:27

3 Answers3

3

There are many ways to resolve this, I have already answered few Here

Alternatives includes Non-MS365 Versions as well.

enter image description here

Using BYROW( ), CONCAT( ) & REPT( )

• Formula used in cell B2

=BYROW(A2:A16,LAMBDA(x,CONCAT(REPT(I2:S2,x=I3:S17))))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • To cover for duplicates, you could return multiple delimited matches by replacing `CONCAT(REPT(I2:S2,x=I3:S17))` with e.g. `TEXTJOIN(", ",1,REPT(I2:S2,x=I3:S17))`. – VBasic2008 Jul 08 '23 at 23:39
  • Don't think there will be duplicates. Because those are Stock Tickers for each kind of a category so having chances of duplicates is almost 0, I believe. – Mayukh Bhattacharya Jul 08 '23 at 23:41
  • 1
    I agree. I just wanted to point out how flexible your solution is. Someone else might need it. I would need to change quite a lot to make it happen in my solution since it only returns the first match looking by rows. – VBasic2008 Jul 08 '23 at 23:42
  • Sir, I had no intention to answer this question posted only since I saw an answer here, however it should have been closed tagged as duplicate. – Mayukh Bhattacharya Jul 08 '23 at 23:46
  • IMO, it's debatable but I linked this (OP's) post to the other anyway. – VBasic2008 Jul 08 '23 at 23:50
  • Interesting, I am not sure a stock belongs to just one ETF (related to industry sectors), the columns names represents ETFs, as per my understanding, now you make me doubt if that is the case in the stock market. Both solutions are great! I added mine, I think it works better for a large dataset, worth to verify it. – David Leal Jul 09 '23 at 04:14
  • @MayukhBhattacharya similar idea using `CONCAT` as follows: `=BYROW(A2:A16,LAMBDA(x, CONCAT(IF(I3:S17=x,I2:S2,""))))`, `IF` is more intuitive to understand than `REPT` in this case in my opinion. – David Leal Jul 09 '23 at 04:42
  • @MayukhBhattacharya and `REPT` does an unnecessary cast to string, **not relevant** for this case, because the header are texts. It can be avoided with the formula in my previous comment, since it doesn't transform the header values. In general I would say it is better not transform the data unless it is really necessary. – David Leal Jul 09 '23 at 05:45
2

Match Column, Return Header (Multiple Results)

  • It's basically the same as presented here, noticed by Mayukh Bhattacharya in your comments, the only difference being that it spills multiple matching results achieved by using the BYROW function.

enter image description here

=LET(lcRange,A2:A11,sTable,D1:F4,NoMatch,"Nope",
    sh,TAKE(sTable,1),sd,TOCOL(DROP(sTable,1)),sCols,COLUMNS(sh),
BYROW(lcRange,LAMBDA(r,
    IFERROR(INDEX(sh,,MOD((XMATCH(r,sd)-1),sCols)+1),NoMatch))))

Input Variables

lcRange - Lookup (Single) Column Range
sTable  - Source Table
NoMatch - If No Match Is Found

Other Variables

sh      - Source Headers
sd      - Source Data
sCols   - Number of Source Columns
r       - Each Lookup Cell (Row) Value
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
1

Here another alternative, using @VBasic2008's sample data:

=BYROW(E2:E11,LAMBDA(x,IFERROR(@TOCOL(IF(A2:C4=x,A1:C1,NA()),2),"Not Found")))

Here is the output: output

@-operator is used in case of duplicated values. In that case, it returns the first column header value found. IFERROR is used just in case the value was not found. TOCOL function is very efficient, so it would be suitable for a large dataset too.

If you want to return all the headers in case of duplicated values, then use the following:

=BYROW(E2:E11,LAMBDA(x, 
 IFERROR(TEXTJOIN(", ",,TOCOL(IF(A2:C4=x,A1:C1,NA()),2)),"Not Found")))

In case of non-duplicated values, the follow works too:

=BYROW(E2:E11,LAMBDA(x,LET(z,CONCAT(IF(A2:C4=x,A1:C1,"")),
 IF(z="","Not Found",z))))

The IF condition is added to treat the case of not found. Similar idea used by @MayukhBhattacharya. If you don't want to handle this case, it results in a shorter approach:

=BYROW(E2:E11,LAMBDA(x,CONCAT(IF(A2:C4=x,A1:C1,""))))
David Leal
  • 6,373
  • 4
  • 29
  • 56