0

Having the nest Formula:

 "=IF(Hoja1!$A$4=$A$15:$A$22),IF($B$4=$B$15:$B$22),IF($F$15:$F$22=0,$A$15:$A$22)))"

The resulting array is like so:

{FALSE\FALSE\FALSE\FALSE\FALSE\"Title 6"\FALSE\FALSE}

get an array that is set of booleans, and in this case I get String, but what I want to get is Ranges so I can know the position of that gotten non False result in the resulting array.

I know I could do the same using loops in VBA but my goal was to make it using formulas. I don't know, maybe there is some built-in function that retrieves ranges that I don't know of.

Alternatively, I thought that having a known Range like $A$15:$F$22 I could get the index numbers of the resulting array that has a non false value and make a Range.Cells(index1, index2) using the 2 indexes of the array.

Also, I thought that using the MATCH function could do it. For example 

Match(<>False, {false\"string value"},0)

And so retrieve the row number that I can then use in a .Cell(). But It does not work. Can I do also a excluding match?

What do you suggest? Any easy/fast solution for this?

Reference to get the date: Range Search criteria

Data source Data source

Lorthas
  • 376
  • 3
  • 11
  • Can you mock up some data and expected output. Please include the final expected output and not the intermediate step seen here. There are probably ways to skip the intermediate steps and go right to the correct final output. – Scott Craner Mar 23 '20 at 14:46
  • @ScottCraner updated question with tables. – Lorthas Mar 23 '20 at 15:00
  • In vba, the quickest is to load the used range in to a Variant Array and loop the Array to find the matching line and return the value. – Scott Craner Mar 23 '20 at 15:25
  • With Formula you would use SUMIFS() to return the correct date. – Scott Craner Mar 23 '20 at 15:26
  • I think I found a solution using almost only formulas. I will post the answer wheN I try it further. – Lorthas Mar 23 '20 at 15:28
  • For formula see here: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Mar 23 '20 at 15:31
  • @ScottCraner I posted an answer. That is what I wanted to do, basically. I think I explain myself quite badly, if someone understands the solution and can explain it better feel free to edit the answer. The idea was to use almost only formulas to search for a Range, not a value, based on conditions using array formulas. – Lorthas Mar 23 '20 at 16:20

1 Answers1

0

I found a solution that was almost in front of my eyes.

I haven't tried using more than one possible search in an array constant (result of an aray formula). It works for sure in array constant results that has one non empty/non-false results.

Using MATCH() was the solution.

Using the previous conditional formula I got an array with all results FALSE, except for one. Since the non empty/false result is a String and I want to know what is the range that result is in, using the data source range I extract that cell using the row index taken from the MATCH function, and then with simple VBA I have the range I wanted, like so.

Dim F As String
Dim Res As Integer
Dim R As Range

Set R = Range("$A$15:$A$22")
F = "=MATCH($A$4,IF($A$4=$A$15:$A$22),IF($B$4=$B$15:$B$22),IF($F$15:$F$22=0,$A$15:$A$22)))),0)"

'We get the index number of the desired value in the array that
we got in *IF* parts of the array formula.
Res = Hoja1.Evaluate(F)

'Since The position in the array of the element we want is the same as the row number inside the range the value we searched for is in, we can get that range/cell easily.
Set R = R.Cells(Res, 1) 'Or the column I want.

Debug.Print R.Address

The result is $A$18.
As we expected it matches, since inside the $A$15$:$A$22 the value we looked for is in the 4th row inside that source range. We can get other columns for that match as well.

Lorthas
  • 376
  • 3
  • 11