lookup value across multi columns and rows in excel

[happi3friend]

There's no primary key so can't use xlookup as many of such functions only support array (either row or column) or require a primary key identifier

For example, if I want to search for '27-Jan-21' and return the corresponding name value, what formula can achieve that in excel?

Note:
1) There's workarounds such as re-organizing all dates and list them in a column but the thing is for the Name column, it is manually labelled and may not be unique

Sun	Mon	Tues	Wed	Thurs	Fri	Sat	Name
3-Jan-21​	4-Jan-21​	5-Jan-21​	6-Jan-21​	7-Jan-21​	8-Jan-21​	9-Jan-21​	3​
10-Jan-21​	11-Jan-21​	12-Jan-21​	13-Jan-21​	14-Jan-21​	15-Jan-21​	16-Jan-21​	5​
17-Jan-21​	18-Jan-21​	19-Jan-21​	20-Jan-21​	21-Jan-21​	22-Jan-21​	23-Jan-21​	3​
24-Jan-21​	25-Jan-21​	26-Jan-21​	27-Jan-21​	28-Jan-21​	29-Jan-21​	30-Jan-21​	2​
31-Jan-21​	1-Feb-21​	2-Feb-21​	3-Feb-21​	4-Feb-21​	5-Feb-21​	6-Feb-21​	9​
7-Feb-21​	8-Feb-21​	9-Feb-21​	10-Feb-21​	11-Feb-21​	12-Feb-21​	13-Feb-21​	8​
14-Feb-21​	15-Feb-21​	16-Feb-21​	17-Feb-21​	18-Feb-21​	19-Feb-21​	20-Feb-21​	7​

 

[ZacharyA]

There's no primary key so can't use xlookup as many of such functions only support array (either row or column) or require a primary key identifier

For example, if I want to search for '27-Jan-21' and return the corresponding name value, what formula can achieve that in excel?

Note:
1) There's workarounds such as re-organizing all dates and list them in a column but the thing is for the Name column, it is manually labelled and may not be unique

Sun	Mon	Tues	Wed	Thurs	Fri	Sat	Name
3-Jan-21​	4-Jan-21​	5-Jan-21​	6-Jan-21​	7-Jan-21​	8-Jan-21​	9-Jan-21​	3​
10-Jan-21​	11-Jan-21​	12-Jan-21​	13-Jan-21​	14-Jan-21​	15-Jan-21​	16-Jan-21​	5​
17-Jan-21​	18-Jan-21​	19-Jan-21​	20-Jan-21​	21-Jan-21​	22-Jan-21​	23-Jan-21​	3​
24-Jan-21​	25-Jan-21​	26-Jan-21​	27-Jan-21​	28-Jan-21​	29-Jan-21​	30-Jan-21​	2​
31-Jan-21​	1-Feb-21​	2-Feb-21​	3-Feb-21​	4-Feb-21​	5-Feb-21​	6-Feb-21​	9​
7-Feb-21​	8-Feb-21​	9-Feb-21​	10-Feb-21​	11-Feb-21​	12-Feb-21​	13-Feb-21​	8​
14-Feb-21​	15-Feb-21​	16-Feb-21​	17-Feb-21​	18-Feb-21​	19-Feb-21​	20-Feb-21​	7​

Have you considered using PowerQuery to clean the data and unpivot all other columns except Name column? And then do a simple lookup / index match?

 

[davidktw]

There's no primary key so can't use xlookup as many of such functions only support array (either row or column) or require a primary key identifier

For example, if I want to search for '27-Jan-21' and return the corresponding name value, what formula can achieve that in excel?

Note:
1) There's workarounds such as re-organizing all dates and list them in a column but the thing is for the Name column, it is manually labelled and may not be unique

Sun	Mon	Tues	Wed	Thurs	Fri	Sat	Name
3-Jan-21​	4-Jan-21​	5-Jan-21​	6-Jan-21​	7-Jan-21​	8-Jan-21​	9-Jan-21​	3​
10-Jan-21​	11-Jan-21​	12-Jan-21​	13-Jan-21​	14-Jan-21​	15-Jan-21​	16-Jan-21​	5​
17-Jan-21​	18-Jan-21​	19-Jan-21​	20-Jan-21​	21-Jan-21​	22-Jan-21​	23-Jan-21​	3​
24-Jan-21​	25-Jan-21​	26-Jan-21​	27-Jan-21​	28-Jan-21​	29-Jan-21​	30-Jan-21​	2​
31-Jan-21​	1-Feb-21​	2-Feb-21​	3-Feb-21​	4-Feb-21​	5-Feb-21​	6-Feb-21​	9​
7-Feb-21​	8-Feb-21​	9-Feb-21​	10-Feb-21​	11-Feb-21​	12-Feb-21​	13-Feb-21​	8​
14-Feb-21​	15-Feb-21​	16-Feb-21​	17-Feb-21​	18-Feb-21​	19-Feb-21​	20-Feb-21​	7​

What should be the return value of 27-Jan-21, is it 2?

That means lookup for any dates on each row gives the rightmost value under the Name column?

 

[happi3friend]

Have you considered using PowerQuery to clean the data and unpivot all other columns except Name column? And then do a simple lookup / index match?

interesting, personally never learnt powerquery/pivot table
will look into it

What should be the return value of 27-Jan-21, is it 2?

That means lookup for any dates on each row gives the rightmost value under the Name column?

yup, it should return 2

I'm sure you can code it to run in VBA but not sure if there's a simple formula or function to mimic it

Currently what I managed to do is
1) find day of '27-Jan-21'
2) Find the column that matches the day
3) Do xlookup on that column and return the Name value

 

[ZacharyA]

interesting, personally never learnt powerquery/pivot table
will look into it

yup, it should return 2

I'm sure you can code it to run in VBA but not sure if there's a simple formula or function to mimic it

Currently what I managed to do is
1) find day of '27-Jan-21'
2) Find the column that matches the day
3) Do xlookup on that column and return the Name value

I think you can learn how to load a data table into PowerQuery in 15mins from ChatGPT and then the rest is simple once you transformed the "3D" date range data into a simple table "2D".

 

[davidktw]

 

[davidktw]

Alternative formula

 

[davidktw]

Just to give some insights to these formulas.

XLOOKUP is a filter logic,
OR is a reduction logic,
BYROW is a iteration logic

Formulas are not so different from your imperative programming style done in a functional approach. You can think about formulas as functional programming.

 

[davidktw]

Alternative formula

Here TRANSPOSE is just to satisfy CONCAT which is a join logic,
BYROW is iteration logic,
IF is obviously your branching logic,
INDEX is really just array indexing logic (verbose if you ask me, better be simply ARR(i) syntax),
COUNTIF is your aggregate logic

 

[happi3friend]

Just to give some insights to these formulas.

XLOOKUP is a filter logic,
OR is a reduction logic,
BYROW is a iteration logic

Formulas are not so different from your imperative programming style done in a functional approach. You can think about formulas as functional programming.

=XLOOKUP(TRUE, BYROW(MAP(A2:G8, LAMBDA(a, a=B11)), LAMBDA(r, OR(r))), H2:H8)

took me awhile just to understand this...

(MAP(A2:G8, LAMBDA(a, a=B11)) - search for the date entered to each cell in the range, returning true if found, false if not found, as array

BYROW((MAP(A2:G8, LAMBDA(a, a=B11)), LAMBDA(r, OR(r))) - check for true in each element in array row-wise

XLOOKUP - if true found, return corresponding name value

I need to master this LAMBDA function. It's very useful. For the other functions, I never used them before (and don't even notice they exist!). Will note it down.

As for the 2nd soln, I will leave it to another day

 

[davidktw]

=XLOOKUP(TRUE, BYROW(MAP(A2:G8, LAMBDA(a, a=B11)), LAMBDA(r, OR(r))), H2:H8)

took me awhile just to understand this...

(MAP(A2:G8, LAMBDA(a, a=B11)) - search for the date entered to each cell in the range, returning true if found, false if not found, as array

BYROW((MAP(A2:G8, LAMBDA(a, a=B11)), LAMBDA(r, OR(r))) - check for true in each element in array row-wise

XLOOKUP - if true found, return corresponding name value

I need to master this LAMBDA function. It's very useful. For the other functions, I never used them before (and don't even notice they exist!). Will note it down.

As for the 2nd soln, I will leave it to another day

Just remove each outer formula meaning when it is A(B(…)) remove A and observe what array is created in the excel sheet, put back each wrapping function as you debug, you will see what I am doing.

LAMBDA terminology was coined from decades back something called lambda calculus. It is the cornerstone for functional programming. Actually I would say it is a mathematical theorem for all programming languages. Today Lambda is more referring to anonymous functions. In a mathematical concept, functions are not necessarily named or labelled.

Eg:

x,y->x+y

is simply a notation for a lambda function that take in 2 arguments and evaluate to the sum of the 2 arguments.

Today a lot of programming languages uses lambda synonymously to anonymous. Hope this brief history lesson helps in your understanding.

Play with it.

 

[davidktw]

u have a phd in computing? were u trained in smv, coq?

Only a degree in Computer Science. Do one need a PHD to know these knowledge? I studied Compiler Design and Programming Languages design courses during my Uni days.

But even without these courses, because of one’s knowledge with functional programming languages like LISP and Haskell, wouldn’t it intrigue one why something is called Lambda across the industry and one go search up about it?

It is all about breadth knowledge and the passion to know them.