Lookup over multiple columns & rows

[paulsolar]

Hi All

I've been trying to figure out how to do this for a while without using a massive formula to do it.

I have a range A26:H38, Column A has engineers names Column B has the job numbers they have carried out on Sunday, C has Mondays jobs and so on to Saturday, there is only one job number per day per engineer and they are unique.

I need to hide the rows A:H to stop anyone altering anything.

I have created a list of all the job for the week numbers in column O starting in row 3, what i want to do is put the name of the engineer who carried out the work next to the job number in column in column N.

Is there an easy easy way to do this? I'm not very good with formula

Cheers

Paul

 

[Fluff]

How about

=XLOOKUP(O3,TOCOL($B$26:$H$38),TOCOL(IF(SEQUENCE(,7),$A$26:$A$38)),"")

 

[paulsolar]

How about

=XLOOKUP(O3,TOCOL($B$26:$H$38),TOCOL(IF(SEQUENCE(,7),$A$26:$A$38)),"")

Hi Fluff, long time no speak, thanks as always for your prompt reply

do i enter this as an array?

 

[Fluff]

do i enter this as an array?

Nope, you never need to do that with 365

 

[paulsolar]

Hi Fluff, long time no speak, thanks as always for your prompt reply

do i enter this as an array?

Hi Fluff, I'm getting this error message "Array arguments to XLOOKUP are of different size."

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[paulsolar]

Hi Fluff, I'm getting this error message "Array arguments to XLOOKUP are of different size."

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Fluff, you will have noticed i never mentioned Saturdays result, i expect you will get your own back this Saturday

I'm trying to work this out for myself now as I'm intrigued, what does this part of the formula do "TOCOL(IF(SEQUENCE(,7)"

 

[Fluff]

Saturday was just are hopefuls team, not a proper one.

what does this part of the formula do "TOCOL(IF(SEQUENCE(,7)"

It effectively copies the values in col A 7 times horizontally & then converts into a column

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Blackdown		Blackdown	Blackdown	Blackdown	Blackdown	Blackdown	Blackdown	Blackdown		Blackdown
3	Longbridge		Longbridge	Longbridge	Longbridge	Longbridge	Longbridge	Longbridge	Longbridge		Blackdown
4	Cinderford East		Cinderford East	Cinderford East	Cinderford East	Cinderford East	Cinderford East	Cinderford East	Cinderford East		Blackdown
5											Blackdown
6											Blackdown
7											Blackdown
8											Blackdown
9											Longbridge
10											Longbridge
11											Longbridge
12											Longbridge
13											Longbridge
14											Longbridge
15											Longbridge
16											Cinderford East
17											Cinderford East
18											Cinderford East
19											Cinderford East
20											Cinderford East
21											Cinderford East
22											Cinderford East
Data

Cell Formulas
Range	Formula
C2:I4	C2	=IF(SEQUENCE(,7),A2:A4)
K2:K22	K2	=TOCOL(C2#)
Dynamic array formulas.