vlookup at its limit

[Dummy Excel]

Hi All,
Ive got a google sheet which automatically updates once my drivers log a fault with their vehicle.
The action status of the vehicle changes from available to unavailable when the repairer places the vehicle in-progress meaning they are working on it.
i then use vlookups to match the registration of the vehicle to determine what is available and what is unavailable
The "bug" in my system is that there could be 2 fault reports for the same vehicle at the same time and depending on the faults could need to go to two different repairers ie faults could be; needs new tyres and flat batteries and wont start

because of this, the faults will be closed out at different times.
The issue i have is that vlookup finds the first match and displays the results. I need a formula that checks the registration, then checks the status and if it finds a status of "unavailable" leave the status "unavailable"

[RANGE=cls:xl2bb-100][XR][XH=cs:5]Book1[/XH][/XR][XR][XH][/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][/XR][XR][XH]1[/XH][XD=h:l|cls:fx][FORMULA=Rego Number]Rego Number[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Reference #]Reference #[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Action Status]Action Status[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Final Status]Final Status[/FORMULA][/XD][/XR][XR][XH]2[/XH][XD=h:l|cls:fx][FORMULA=apples]apples[/FORMULA][/XD][XD=h:r|cls:fx][FORMULA=11244158]11244158[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Completed (Repairs completed, Available and safe to use)]Completed (Repairs completed, Available
and safe to use)[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Available]Available[/FORMULA][/XD][/XR][XR][XH]3[/XH][XD=h:l|bc:ffff00|cls:fx][FORMULA=banana]banana[/FORMULA][/XD][XD=h:r|bc:ffff00|cls:fx][FORMULA=11240649]11240649[/FORMULA][/XD][XD=h:l|bc:ffff00|cls:fx][FORMULA=Completed (Repairs completed, Available and safe to use)]Completed (Repairs completed, Available
and safe to use)[/FORMULA][/XD][XD=h:l|bc:ffff00|cls:fx][FORMULA=Available]Available[/FORMULA][/XD][/XR][XR][XH]4[/XH][XD=h:l|cls:fx][FORMULA=apples]apples[/FORMULA][/XD][XD=h:r|cls:fx][FORMULA=11236230]11236230[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Completed (Repairs completed, Available and safe to use)]Completed (Repairs completed, Available
and safe to use)[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Available]Available[/FORMULA][/XD][/XR][XR][XH]5[/XH][XD=h:l|cls:fx][FORMULA=fig]fig[/FORMULA][/XD][XD=h:r|c:ff0000|cls:fx][FORMULA=11284309]11284309[/FORMULA][/XD][XD=h:r][/XD][XD=h:l|cls:fx][FORMULA=Available]Available[/FORMULA][/XD][/XR][XR][XH]6[/XH][XD=h:l|bc:ffff00|cls:fx][FORMULA=banana]banana[/FORMULA][/XD][XD=h:r|bc:ffff00|c:ff0000|cls:fx][FORMULA=11284364]11284364[/FORMULA][/XD][XD=h:l|bc:ffff00|cls:fx][FORMULA=In-Progress (Unavailable, tagged out and not safe to use)]In-Progress (Unavailable, tagged out and
not safe to use)[/FORMULA][/XD][XD=h:l|bc:ffff00|cls:fx][FORMULA=Unavailable]Unavailable[/FORMULA][/XD][/XR][XR][XH=cs:5][RANGE][XR][XD]Sheet2[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]B1[/XD][XD]Rego Number[/XD][/XR][XR][XD]B2[/XD][XD]apples[/XD][/XR][XR][XD]B3[/XD][XD]banana[/XD][/XR][XR][XD]B4[/XD][XD]apples[/XD][/XR][XR][XD]B5[/XD][XD]fig[/XD][/XR][XR][XD]B6[/XD][XD]banana[/XD][/XR][XR][XD]C1[/XD][XD]Reference #[/XD][/XR][XR][XD]C2[/XD][XD]11244158[/XD][/XR][XR][XD]C3[/XD][XD]11240649[/XD][/XR][XR][XD]C4[/XD][XD]11236230[/XD][/XR][XR][XD]C5[/XD][XD]11284309[/XD][/XR][XR][XD]C6[/XD][XD]11284364[/XD][/XR][XR][XD]D1[/XD][XD]Action Status[/XD][/XR][XR][XD]D2[/XD][XD]Completed (Repairs completed, Available and safe to use)[/XD][/XR][XR][XD]D3[/XD][XD]Completed (Repairs completed, Available and safe to use)[/XD][/XR][XR][XD]D4[/XD][XD]Completed (Repairs completed, Available and safe to use)[/XD][/XR][XR][XD]D6[/XD][XD]In-Progress (Unavailable, tagged out and not safe to use)[/XD][/XR][XR][XD]E1[/XD][XD]Final Status[/XD][/XR][XR][XD]E2[/XD][XD]Available[/XD][/XR][XR][XD]E3[/XD][XD]Available[/XD][/XR][XR][XD]E4[/XD][XD]Available[/XD][/XR][XR][XD]E5[/XD][XD]Available[/XD][/XR][XR][XD]E6[/XD][XD]Unavailable[/XD][/XR][/RANGE]

The formula needs to be in column H for my summary page

[RANGE=cls:xl2bb-100][XR][XH=cs:3]Book1[/XH][/XR][XR][XH][/XH][XH]C[/XH][XH]H[/XH][/XR][XR][XH]2[/XH][XD=h:l|fw:b]Rego[/XD][XD=h:c]Rego Availability[/XD][/XR][XR][XH]3[/XH][XD=h:l]apples[/XD][XD=h:c|cls:fx][FORMULA==VLOOKUP(C3,Sheet2!B1:AG6,32,0)]Available[/FORMULA][/XD][/XR][XR][XH]4[/XH][XD=h:l|bc:ffff00]banana[/XD][XD=h:c|bc:ffff00|cls:fx][FORMULA==VLOOKUP(C4,Sheet2!B2:AG7,32,0)]Available[/FORMULA][/XD][/XR][XR][XH]5[/XH][XD=h:l]fig[/XD][XD=h:c|cls:fx][FORMULA==VLOOKUP(C5,Sheet2!B3:AG8,32,0)]Available[/FORMULA][/XD][/XR][XR][XH=cs:3][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]H3[/XD][XD]=VLOOKUP(C3,Sheet2!B1:AG6,32,0)[/XD][/XR][XR][XD]H4[/XD][XD]=VLOOKUP(C4,Sheet2!B2:AG7,32,0)[/XD][/XR][XR][XD]H5[/XD][XD]=VLOOKUP(C5,Sheet2!B3:AG8,32,0)[/XD][/XR][/RANGE]

all your help is greatly appreciated
thanks
Sam

 

[FormR]

I need a formula that checks the registration, then checks the status and if it finds a status of "unavailable" leave the status "unavailable"

Hi, how about something like..:

=IF(COUNTIFS(Sheet2!B:B,C3,Sheet2!E:E,"Unavailable"),"Unavailable","Available")

 

[Dummy Excel]

thank you so much, this has made things so much easier due to a fleet of 350 vehicles
ive never seen a countif"s" before, time to do some research
i really appreciate your help

 

[hiker95]

ive never seen a countif"s" before, time to do some research

Dummy Excel,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 3/2/2018

https://www.mrexcel.com/forum/excel...dations-online-excel-courses.html#post5024983