Forum Discussion
Using an If Statement within a VLookup across sheets
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.
- 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.
- 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?You can highlight it by conditional formatting
or using a formula to count the quantity of that vehicle is duplicates
=COUNTIF(VehicleList,Vehicle)>1
