Formula to lookup values and copy to another sheet

[AndyJM87]

Hello, I will try and explain as best as I can...

I need to copy the the values from "Data" sheet "Column G" across to "Mod Inst Audit Register" sheet in the rows under each serial number.

I tried this =VLOOKUP($A$6,Data!$C:$G,5,FALSE)

All this does is copy across "Embodied". From the images below, you can see that Mod No. 9 shows "Embodied", but in the other sheet shows "Applicable". But it does not copy "Applicable" across, just fills the whole row with "Embodied".

I tried XLOOKUP and still have the same results.

Can someone please modify my formula to copy all of Column G across to the Rows under each serial number.

Thanks

 

[jasonb75]

You can't do that with any of the lookup functions, if you read the help for them they all say that they return the 'first' matching record, which is exactly what you're getting.

If you've tried xlookup then that means you should have the filter function, you can adjust the range as needed but I would advise against using the entire column.

=TRANSPOSE(FILTER(Data!$G$2:$G$2000,Data!$C$2:$C$2000=$A6))