Forum Discussion

Copper Contributor

May 13, 2019

Solved

Version of Lookup formula?

Have a table dates across the top and items down the side. The body of the table gets populated with "1s" which auto formats to a cell colour. Id like to create a column that looks up when a ...

excel

Formulas and Functions

Detlef_Lewin
May 13, 2019
warwick_Hope
=LOOKUP(PI(),B2:Z2,B$1:Z$1)

Detlef_Lewin

Silver Contributor

May 16, 2019

warwick_Hope

That would be:

=INDEX(B$1:Z$1,MATCH(1,B2:Z2,0))

Twifoo

Silver Contributor

May 16, 2019

I’m just curious. What would be the equivalent formula to achieve the same result using LOOKUP and OUR sexy PI() as the lookup_value argument?

Detlef_Lewin
Silver Contributor
May 16, 2019
Dear Twifoo,

I have no idea. The problem is that LOOKUP() works from right to left.
- Twifoo
  Silver Contributor
  May 17, 2019
  Hello Detlef_Lewin,
  Just to satisfy my curiosity, this is the equivalent of the INDEX-MATCH formula using LOOKUP-PI.
  =LOOKUP(PI(),
  2/(1/COLUMN(B2:Z2)=AGGREGATE(14,4,1/COLUMN(B2:Z2)*((B2:Z2)=1),1)),
  B1:Z1)
  Indubitably, INDEX-MATCH formula is preferred but the foregoing formula satisfied my curiosity!
  - Detlef_Lewin
    Silver Contributor
    May 18, 2019
    Twifoo
    I expected that a possible solution would be considerably longer and more complex.