Formula to match information from a source sheet

[GLV]

Hi, everyone,


I attached a file, in hope that you will understand my need. I tried using Vlookup, but as you know, vlookup only finds first value. And my Excel knowledge is very limited.

Basically, I need a formula to take the information from Database sheet (source) and place them in the CAR sheet, matching the date and the CAR name. I need this because i have a bigger number of cars to which i need to create a Daily activity sheet for vehicles.And if I could automatically import the information it would save me tons on time.

Hope you will take some time to check the attachement. Many Thanks !

[arnelgp]

is this close?

[georgiboy]

One single formula in Excel 365

[GLV]

Thanks for your responses!
I attached another workbook, because i didnt add all the information.
So... in database sheet i want to add all the cars with information from all the year long. There will be several sheets corresponding to each car (car1, car2 and so on). Each car will have 12 tables, from january to december.
Each table in every car sheet should take it's information from database sheet.
Basically: Car1 has 12 tables in it, from january from december (in the attached example i only have october and november). I want the formula to search in the database sheet information matching with car1 and it's corresponding month. Including the fuel info from the bottom on table (which can be optional).
I really tried modifing your formulas, but i couldnt figuring out anything. My knowledge is tending to 0.

Thank you for your time!

[georgiboy]

Maybe like on the attached?

[GLV]

I tested it, added some more data and seems to be working great. I have a #SPILL! error on CAR1, can't figure out what the problem is. I deleted the duplicate values, the error persists. But CAR2 has no problems so far. You are the man!
Any thoughts on Fuel area (i greened it out, on the bottom of the table) ?
Many thanks !

[georgiboy]

I updated my attachment to include the fuel part, I will have to investigate the spill error tomorrow although they are usually down to the formula not having enough room to spill into.

[GLV]

I updated my attachment to include the fuel part, I will have to investigate the spill error tomorrow although they are usually down to the formula not having enough room to spill into.


Only if you have the time. I myself checked the error, there is the same number on cells on each sheet. But as i mentioned, my Excel skill is veeeery slim.
You were of great help! Thank you very much !

[georgiboy]

It is because you have data in G2:J80, if you clear those cells the formula will spill. If any cells have data in where the formula is to spill you will get a spill error.

[georgiboy]

I have updated the file i had with some XLOOKUP instead of filter for the main data. Fuel bit included.

[GLV]

Thank you very much! It is what i needed. You are awesome!

Edit:

I added all the months for one car (b102pmh is the number plate) to fully-test it aaaand and i can't figure out the #SPILL! on the fuel are on january. All the other months are working, but January won't. I tried using the formula from the earlier methods, but still the error persists.

[georgiboy]

You can edit that formula to the below, you could also edit the other fuel calculations in the same way.

=IFERROR(FILTER(Database!$K$2:$M$7000,(Database!$A$2:$A$7000=E2)*(MONTH(Dat abase!$K$2:$K$7000)=MONTH(E1))*(Database!$K$2:$K$7000>0)),"")

[GLV]

That did the trick !
Thank you, I wish you all the best !

[GLV]

One more quick and maybe dumb question: is this working only on certain versions on Office (particulary 365?) ? Tried it on Office home and business 2019 and i got the #NAME? error. I didnt change anything on the workbook.
The formulas changed like this
=_xlfn.LET(_xlpm.f,_xlfn.XLOOKUP(A373&B373,Database!$A$2:$A$7000&Database!$B$2:$B$7000,Database!$C$2:$J$7000,"",0),_xlfn.MAP(_xlpm.f,_xlfn.LAMBDA(_xlpm.x,IF(_xlpm.x=0,"",_xlpm.x))))

[Aussiebear]

Office 365 for Lambda. Will not work in prior versions.

[arnelgp]

which means back to old index/match for compatibility.

[GLV]

Anyone willing to adapt it for the non 365 versions of office?

[arnelgp]

i think Filter() is easy to do, you just have to make a little effort since GB already gave you some worksheet with example in it.
try to learn from the example given and google those you are not familiar with.