SOLVED
Home

Version of Lookup formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-564041%22%20slang%3D%22en-US%22%3EVersion%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564041%22%20slang%3D%22en-US%22%3E%3CP%3EHave%20a%20table%20dates%20across%20the%20top%20and%20items%20down%20the%20side.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20body%20of%20the%20table%20gets%20populated%20with%20%221s%22%20which%20auto%20formats%20to%20a%20cell%20colour.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EId%20like%20to%20create%20a%20column%20that%20looks%20up%20when%20a%20row%20has%20a%20%221%22%20in%20and%20then%20returns%20the%20date%20in%20the%20column%20header.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20struggling%20to%20find%20what%20sort%20of%20formula%20to%20use%20for%20it%20any%20guidance%20would%20be%20appriciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-564041%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564120%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564120%22%20slang%3D%22en-US%22%3EI%20suppose%20you%20want%20to%20return%20the%20date%20IF%20the%20cell%20contains%201%3B%20otherwise%2C%20you%20want%20to%20return%20empty%20text.%20I%20hope%20you%20now%20know%20what%20formula%20to%20use.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564125%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341463%22%20target%3D%22_blank%22%3E%40warwick_Hope%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DLOOKUP(PI()%2CB2%3AZ2%2CB%241%3AZ%241)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564127%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564127%22%20slang%3D%22en-US%22%3E%3CP%3EI%20had%20tried%20using%20%22IF%22%20but%20the%20%221%22%20could%20be%20in%20any%20cell%20across%20the%20table%2C%20and%20i%20didnt%20want%20to%20have%20to%20create%20a%20long%20formula%20with%2020%20%22IF%22%20Logical%20tests!%3CBR%20%2F%3EIs%20there%20a%20shorter%20way%20of%20achieving%20the%20same%20thing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564132%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564132%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3CBR%20%2F%3EMany%20thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572462%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20more%20than%20one%20cell%20filled%20in%20a%20row%2C%20the%20formula%20picks%20up%20the%20cell%20furthest%20to%20the%20right.%3C%2FP%3E%3CP%3EThis%20is%20fine%20in%20the%20main%20but%20just%20wanted%20to%20know%20if%20there%20was%20any%20way%20of%20changing%20it%20to%20pick%20the%20first%20cell%20it%20comes%20to%3F%20(furthest%20to%20the%20left%3F)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575738%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575738%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341463%22%20target%3D%22_blank%22%3E%40warwick_Hope%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20would%20be%3A%3C%2FP%3E%3CPRE%3E%3DINDEX(B%241%3AZ%241%2CMATCH(1%2CB2%3AZ2%2C0))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575934%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575934%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20just%20curious.%20What%20would%20be%20the%20equivalent%20formula%20to%20achieve%20the%20same%20result%20using%20LOOKUP%20and%20OUR%20sexy%20PI()%20as%20the%20lookup_value%20argument%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-576236%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576236%22%20slang%3D%22en-US%22%3E%3CP%3EDear%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20no%20idea.%20The%20problem%20is%20that%20LOOKUP()%20works%20from%20right%20to%20left.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-584365%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-584365%22%20slang%3D%22en-US%22%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%2C%3CBR%20%2F%3EJust%20to%20satisfy%20my%20curiosity%2C%20this%20is%20the%20equivalent%20of%20the%20INDEX-MATCH%20formula%20using%20LOOKUP-PI.%3CBR%20%2F%3E%3DLOOKUP(PI()%2C%3CBR%20%2F%3E2%2F(1%2FCOLUMN(B2%3AZ2)%3DAGGREGATE(14%2C4%2C1%2FCOLUMN(B2%3AZ2)*((B2%3AZ2)%3D1)%2C1))%2C%3CBR%20%2F%3EB1%3AZ1)%3CBR%20%2F%3EIndubitably%2C%20INDEX-MATCH%20formula%20is%20preferred%20but%20the%20foregoing%20formula%20satisfied%20my%20curiosity!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-612505%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612505%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341463%22%20target%3D%22_blank%22%3E%40warwick_Hope%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20dynamic%20arrays%20are%20released%2C%20multiple%20matches%20will%20be%20returned%20by%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20FILTER(%20Date%2C%20Criterion%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ETo%20do%20something%20similar%20now%20would%20need%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SMALL(%20IF(%20Criterion%2C%20Date%20)%2C%20%7B1%2C2%2C3%7D%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20dates%20could%20also%20be%20listed%20in%20a%20wrapped%20cell%20using%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20TEXTJOIN(%20CHAR(10)%2C%20TRUE%2C%20IF(%20Criterion%2C%20TEXT(Date%2C%22dd%20mmm%22)%2C%20%22%22%20)%20)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-612558%22%20slang%3D%22en-US%22%3ERe%3A%20Version%20of%20Lookup%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20expected%20that%20a%20possible%20solution%20would%20be%20considerably%20longer%20and%20more%20complex.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
warwick_Hope
New Contributor

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 row has a "1" in and then returns the date in the column header.

 

Just struggling to find what sort of formula to use for it any guidance would be appriciated

11 Replies
I suppose you want to return the date IF the cell contains 1; otherwise, you want to return empty text. I hope you now know what formula to use.
Solution

@warwick_Hope 

=LOOKUP(PI(),B2:Z2,B$1:Z$1)

 

I had tried using "IF" but the "1" could be in any cell across the table, and i didnt want to have to create a long formula with 20 "IF" Logical tests!
Is there a shorter way of achieving the same thing?

@Detlef Lewin
Many thanks!

@Detlef Lewin 

If there is more than one cell filled in a row, the formula picks up the cell furthest to the right.

This is fine in the main but just wanted to know if there was any way of changing it to pick the first cell it comes to? (furthest to the left?)

@warwick_Hope 

That would be:

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

 

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?

Dear @Twifoo,

 

I have no idea. The problem is that LOOKUP() works from right to left.

 

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!

@warwick_Hope 

When dynamic arrays are released, multiple matches will be returned by

= FILTER( Date, Criterion )

To do something similar now would need

= SMALL( IF( Criterion, Date ), {1,2,3} )

The dates could also be listed in a wrapped cell using

= TEXTJOIN( CHAR(10), TRUE, IF( Criterion, TEXT(Date,"dd mmm"), "" ) )

@Twifoo 

I expected that a possible solution would be considerably longer and more complex.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies