VLOOKUP for different layout data

[vitoaiaco]

Hi, 

Just looking for your help please.

You'll find attached a sample spreadsheets with 2 tabs in it. The tab "Trailer mm April 2024" has mileage data (in column F) listed vertically. I would like to layout the mileage info to be added in the 1st tab ("Sheet 1) by matching the date (row 1) and trailer fleet number (column A) and giving the corresponding value from tab "Trailer mm April 2024".

Your help is much appreciated (I'm running office 365)

Thanks

Vito

[#2]

@vitoaiaco 

Insert a pivot table.

Flt and Reg into rows area.

Month in columns area.

KMS in values area.

[#3]

@vitoaiaco 

In E3:

=IFERROR(INDEX('Trailer mm April 24 '!$F$3:$F$1188, XMATCH(1, ('Trailer mm April 24 '!$A$3:$A$1188=$A3)*('Trailer mm April 24 '!$B$3:$B$1188=$B3)*('Trailer mm April 24 '!$C$3:$C$1188=$C3)*('Trailer mm April 24 '!$D$3:$D$1188=E$1))), "")

Fill to the right, then down.

[#4]

@vitoaiaco 

=MAKEARRAY(98,29,LAMBDA(r,c,XLOOKUP(INDEX(E1:AG1,,c)&INDEX(A3:A100,r),'Trailer mm April 2024'!D1:D1188&'Trailer mm April 2024'!A1:A1188,'Trailer mm April 2024'!E1:E1188,"")))

With Office 365 or Excel for the web you can use LAMBDA and MAKEARRAY.