# VLOOKUP for different layout data

Copper Contributor

# VLOOKUP for different layout data

Hi,

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

3 Replies

# Re: VLOOKUP for different layout data

Insert a pivot table.

Flt and Reg into rows area.

Month in columns area.

KMS in values area.

# Re: VLOOKUP for different layout data

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.

# Re: VLOOKUP for different layout data

=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.