Forum Discussion
VLOOKUP for different layout data
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
3 Replies
=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.
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.
