Using an If Statement within a VLookup across sheets

[Loki_bear_21]

Hi,

I have a work sheet which has multiple tabs showing vehicle data I also have a lookup tab so that when I enter a registration it will show me the relevant details and which branch the vehicle belongs to I have used the below formula using an indirect & index for referencing the different sheets with an iferror to show me error's and an if to show blanks when value is empty or 0. =(IFERROR(IF(LEN(VLOOKUP(C11,INDIRECT("'"&INDEX(Sheet_Names,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet_Names&"'!A3:A700"),C11)>0),0))&"'!$A$3:$AA$700"),3,0))=0,"Not Found",VLOOKUP(C11,INDIRECT("'"&INDEX(Sheet_Names,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet_Names&"'!$A$3:$A$700"),C11)>0),0))&"'!$A$3:$AA$700"),3,0)),"")). The problem I have though is when a vehicle is used again at another branch the Lookup will go to the first value now there is a duplicate, I have added another column to show vehicles active yes and no so I want my lookup to now only return the vehicle if active yes which will get rid of the duplicate, However I am struggling to right this in my formula and I'm guessing there needs to be another if statement to tell the lookup to find the registration if active...Someone's help would be greatly appreciated.

Copy of spreadsheet attached.

[#2]

I would combine all data on a single sheet with an added "Fleet" column and ditch the individual Fleet sheets, they lead to a lot of unnecessary complexity in your formulas.

[#3]

Hi
Thanks for responding to me would I not have the same problem on one sheet though? If I combined all on one sheet how would I get my lookup just to show the active vehicles when there are duplicates?

[#4]

@Loki_bear_21 

You can highlight it by conditional formatting

or using a formula to count the quantity of that vehicle is duplicates

=COUNTIF(VehicleList,Vehicle)>1

[#5]

Depends on how you want to determine which vehicle to display?

Perhaps something like the attached, where I have added slicers to filter the table?

[#6]

I want the vehicle to be displayed once it is active which is determined by a yes or no for example vehicle moved from one branch to another the second branch being where the vehicle is now active

[#7]

I know that your current approach is use excel to manage it.
But I would suggest you using MS Access to manage it properly. Specially because MS Access can generate more easily a lot of different reports.