evaluating duplicate inputs

[JoeEason92]

Hello,

I have a database of faults itemised by codes and vehicle numbers. Column C is my vehicle number, Column D is a location reference number and Column G is a Fault description. My formula works to highlight a fault position on the vehicle which has a fault present. Typing the vehicle number into I4 matches with a vehicle Number in column C. Each position has the same formula but with a different location reference range (eg 818100-818131 or 821100-821131). However if there are multiple faults recorded on the same vehicle, this formula will not look past the first instance of matching with the vehicle number and will return a "false" reading when the location reference is out of range. Is there a way to force the formula to evaluate further down column C to find the next instances of faults on the same vehicle in different positions? An example of my formula from one of the fault positions is listed below.

"=IFERROR(IF(AND(I4=INDEX('Restriction data'!C:C,MATCH(I4,'Restriction data'!C:C,0)),AND(INDEX('Restriction data'!D:D,MATCH(I4,'Restriction data'!C:C,0))>=818100,INDEX('Restriction data'!D:D,MATCH(I4,'Restriction data'!C:C,0))<=818131),ISNUMBER(SEARCH("SST LOOU",INDEX('Restriction data'!G:G,MATCH(I4,'Restriction data'!C:C, 0))))),"R-SST","SST"),"SST")"

[#2]

@JoeEason92 

=IFERROR(SMALL(IF(('Restriction data'!$C:$C=Tabelle1!$I$5)*('Restriction data'!$D:$D>=Tabelle1!$J$5)*('Restriction data'!$D:$D<=Tabelle1!$K$5)*('Restriction data'!$G:$G=Tabelle1!$L$5),ROW('Restriction data'!$F:$F)),ROW(1:1)),"")

Does this return a result similar to what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

You can dynamically enter the search criteria in cell I5 to L5. The formula is in cell G8 and filled down.

[#3]

I tried to amend my formula using yours but I can't quite get it to give the same results. For some reason I can't upload a spreadsheet but I've edited yours to better reflect what I have and what I was trying to do. Hopefully you can see the issue I have and why the formula is not cooperating with multiple matches from the vehicle number

@OliverScheurich 

[#4]

@JoeEason92 

For anyone that has 365, FILTER is the relevant function.

= LET(
    isVehicle, (vehicleNumber=vehicle),
    isInRange, (locationNumber>=location1) * (locationNumber<=location2),
    isAsFault, (faultDescription=description),
    criterion, isVehicle * isInRange * isAsFault,
    FILTER(locationNumber, criterion)
  )

That returns location references rather than sheet row numbers but SEQUENCE could be used to return record numbers.

[#5]

I had been editing online as the reference sheet with the fault archive is updated live from another spreadsheet stored on the same cloud storage, so it was easier to display and reference each online. So I haven't had access to VBA or any command lines for this project. However if you think that is the best or only way to achieve what I'm looking for, I can edit it in an offline document and reconnect the reference data afterwards?

[#6]

@JoeEason92 

There may be some confusion here.  As far as I know, there is nothing in the formulas I have written that will not work on-line or on any other hardware platform.  Although it looks like code, the formulas are just regular worksheet formulas that use the Excel 365 functions LET and FILTER (I think Google sheets has also implemented the functions) .  The use of defined names, rather than direct range references, can cause issues because I don't think Name Manager or the AFE have been implemented within Excel 365 online (if that statement needs to be corrected, I would be delighted to hear).

[#7]

@Peter Bartholomew 

FYI, AFE is available on Excel for web

[#8]

@Sergei Baklan 

Thanks Sergei.  That is useful to know.  Perhaps I should try the cloud version of Excel again!  As you will have observed, I only use named arrays to build solutions, so my experience of using Excel on-line from a couple of years ago was not encouraging.

[#9]

@Peter Bartholomew 

Oh, if you don't use VBA, data models (Power Pivot with Power Query), some functions like CELL("filename") and don't try to align text vertically - Excel for web works even better.

[#10]

@Sergei Baklan 

I did go through a phase where every workbook I wrote seemed to require VBA event handlers to operate.  By way of example:

2048 game version for Excel (spreadsheet1.com)

I even used VBA classes and events to synchronise a process diagram created by linking shapes with connecters with the underlying data structure defining the process.  I would be hard pressed to repeat that now but building a solution comprising one Lambda function per sheet offers some compensation!