Looking up (text)values in Excel array

Question

I was searching around on internet and playing around in Excel 2010, but could'nt really find an answer.

For the sake of cleanliness I made an abstract, but the real file is a lot bigger.

In another sheet I want to look up text A and get the output of the relative time (Column B). However, if row 2 extends up to Column Z (or even more) I want all the output times return the same value, as visible for A, B and C in the picture below.

Column D will probably be gone/hidden when I fix the conditional formatting. It's also possible that 'K' comes before 'H' though, so the values may not be in order (as visible for F, G and H

This is the first problem. I tried solving it with INDEX-MATCH, but for an unknown number of rows and columns that's pretty tedious. There are also many blanks, so I need to build safes for that too.

The formula for only 6 columns would look like this:

=IFERROR(INDEX(T0.02!$B$1:$B$100;IF(ISNA(MATCH($A3;T0.02!$D$1:$D$100;0))=FALSE;MATCH($A3;T0.02!$D$1:$D$100;0);IF(ISNA(MATCH($A3;T0.02!$E$1:$E$100;0))=FALSE;MATCH($A3;T0.02!$E$1:$E$100;0);IF(ISNA(MATCH($A3;T0.02!$F$1:$F$100;0))=FALSE;MATCH($A3;T0.02!$F$1:$F$100;0);IF(ISNA(MATCH($A3;T0.02!$G$1:$G$100;0))=FALSE;MATCH($A3;T0.02!$G$1:$G$100;0);IF(ISNA(MATCH($A3;T0.02!$H$1:$H$100;0))=FALSE;MATCH($A3;T0.02!$H$1:$H$100;0);IF(ISNA(MATCH($A3;T0.02!$I$1:$I$100;0))=FALSE;MATCH($A3;T0.02!$I$1:$I$100;0);"")))))));"")

The main question for me is: is it possible to do it an easier way in Excel, or is there a way doing it with VBA. I'm not really 'good' with VBA so I would really need help with that then.

Answer 1

Here's an approach that uses some VBA to simplify the lookup without any helper cells.

The VBA part is this user-defined function (commonly referred to as a UDF) that serves the same purpose as the MATCH function you were trying to use to find the row in which the text is found:

Function FindRow(valueToFind, searchRange As Range, Optional IsCaseSensitive As Variant) As String
   Dim aCell As Range
   Dim lastCell As Range
   If IsMissing(IsCaseSensitive) Then
      IsCaseSensitive = False
   End If
   Set lastCell = searchRange.Cells(searchRange.Rows.Count, searchRange.Columns.Count)
   Set aCell = searchRange.Find(valueToFind, After:=lastCell, LookIn:=xlValues, MatchCase:=IsCaseSensitive, LookAt:=xlWhole)
   FindRow = aCell.Row - searchRange.Cells(1, 1).Row + 1
End Function

What makes this useful is that the VBA FIND function, unlike the worksheet MATCH function, can search through a range than spans several columns and rows. Like MATCH, this VBA function returns the row within the search range that a specified value is found and returns a #VALUE! error if it cannot find the value.

The first two arguments of FindRow are the value to find (or the cell in which the value is located) and the range to look in. There is a third optional argument: if set to TRUE or 1, the find will be case-sensitive; if omitted or set to FALSE or 0, the find will be case-insensitive.

On the second sheet, you will use INDEX in combination with FindRow to look up the time, for example,

=INDEX(Sheet1!$B$2:$B$8,FindRow(A3,Sheet1!$D$2:$Z$9))

with the second argument to FindRow set to the range that contains the Commentary values. While I've shown the search range extending to column Z and row 9, FindRow can handle ranges of any size.

To install the VBA function, first select "Visual Basic" from the Developer tab on the ribbon. Select the workbook name in the Project pane at the upper left (the workbook will be designated as "VBAProject(YourSheetName)" and Insert a Module using the menu bar at top. Finally, paste the function code into the large code pane that appears on the right.

Answer 2

I used column C in your first sheet as temporary column:

1. Column C in the input sheet use:

   =","&D3&","&E3&","&F3&","&G3&","&H3&","&I3&","&J3&","&K3&","&L3&","&M3&","&N3&","&O3&","&P3&","&Q3&","&R3&","&S3&","&T3&","&U3&","&V3&","&W3&","&X3&","&Y3&","&Z3

   - to combine all possible comments into one cell
2. in C3 (and the other cells) in the aggregation sheet: search all of the combined cells in the helper column for the comment and use it in an index function to return the time:

   =IFERROR(INDEX(Sheet1!$B:$B,MATCH(FALSE,ISERROR(FIND(","&A3&",",Sheet1!$C:$C)),0)),"")

   Enter this as an array formula, i.e. use Ctrl-Shift-Enter.

HTH!

Answer 3

You can do this relatively simply with an "array formula" without adding any columns, e.g. for a range up to column Z

=MIN(IF(T0.02!$D$2:$Z$100=$A3;T0.02!$B$2:$B$100))

confirmed with CTRL+SHIFT+ENTER

If there is no match then you get zero, if you want a blank instead, to distinguish between legitimate zero values you can use SMALL with IFERROR, i.e.

=IFERROR(SMALL(IF(T0.02!$D$2:$Z$100=$A3;T0.02!$B$2:$B$100);1);"")