Array formula lookup

[Gene Khalyapin]

Hello,

I got stuck on what seems like a pretty easy calculation. Any ideas why the formula below does not work?
=LOOKUP(1,{0,0,0;0,1,0;1,0,0},{“R1C1”,” R1C2”, ” R1C3”; “R2C1”,” R2C2”, ” R2C3”; “R3C1”,” R3C2”, ” R3C3”})
Needed result: R2C2

Is there a different way to do this? In a very simplified form I need to do the following calculation:
1. I have two data squares (let's say: 3 rows by 3 columns each).
2. I need to find a certain value in square 1 and return the value from square 2 from the same relative location. In other words if the value I need is in cell R2C2 in square 1, I need the correcsponding text from cell R2C2 from suare 2.

If there are two or more values in square 1, I want to return just the first one.
I need a formula solution without adding extra rows or columns.

Thanks,
Gene

 

[hiker95]

Gene Khalyapin,

Is your formula being used in a macro?

Your formula contains spaces and ; characters:
=LOOKUP(1,{0,0,0;0,1,0;1,0,0},{“R1C1”,” R1C2”, ” R1C3”; “R2C1”,” R2C2”, ” R2C3”; “R3C1”,” R3C2”, ” R3C3”})


Try:
=LOOKUP(1,{0,0,0,0,1,0,1,0,0},{“R1C1”,”R1C2”,”R1C3”,“R2C1”,”R2C2”,”R2C3”,“R3C1”,”R3C2”,”R3C3”})

 

[shg]

The array form of LOOKUP only takes two arguments.

I think that's hard to do in a formula, and easy in a UDF if you use ranges rather than literal arrays:

Function GK(vWhat As Variant, _
            rInp As Range, _
            rOut As Range, _
            Optional iDir As XlSearchDirection = xlByRows, _
            Optional bMatchCase As Boolean = False) As Variant
    Dim rFind     As Range
 
    With rInp
        Set rFind = .Find(What:=vWhat, _
                          After:=.Cells(.Rows.Count, .Columns.Count), _
                          SearchOrder:=iDir, _
                          MatchCase:=bMatchCase, _
                          SearchDirection:=xlNext, _
                          SearchFormat:=False)
        If rFind Is Nothing Then
            GK = CVErr(xlErrNA)
        Else
            GK = rOut.Offset(rFind.Row - .Row, _
                             rFind.Column - .Column).Value
        End If
    End With
End Function

E.g., =GK(1, B2:D4, F2:H4)

 

[Aladin Akyurek]

Hello,

I got stuck on what seems like a pretty easy calculation. Any ideas why the formula below does not work?
=LOOKUP(1,{0,0,0;0,1,0;1,0,0},{“R1C1”,” R1C2”, ” R1C3”; “R2C1”,” R2C2”, ” R2C3”; “R3C1”,” R3C2”, ” R3C3”})
Needed result: R2C2
Is there a different way to do this? In a very simplified form I need to do the following calculation:
1. I have two data squares (let's say: 3 rows by 3 columns each).
2. I need to find a certain value in square 1 and return the value from square 2 from the same relative location. In other words if the value I need is in cell R2C2 in square 1, I need the correcsponding text from cell R2C2 from suare 2.
If there are two or more values in square 1, I want to return just the first one.
I need a formula solution without adding extra rows or columns.
Thanks,
Gene

1] LOOKUP requires a sorted match range.
2] Fed with a matrix as match area, like you have {0,0,0;0,1,0;1,0,0}, LOOKUP will pick out the last column of that matrix as the match range, not every column.
3] Fed with a matrix as result area, like you have {"R1C1","R1C2","R1C3";"R2C1","R2C2","R2C3";"R3C1","R3C2","R3C3"}, LOOKUP will error out, expecting a vector from which to return a result.

 

[Gene Khalyapin]

Thank you all for your help!

Is your formula being used in a macro?

No. The lookup range is the result of an argument: (B2:D4=value)*1. I find the needed value in the first cube (array returns TRUE/FALS statements) and multiply by 1. {0,0,0;0,1,0;1,0,0} is the resulting array that shows that the required value is in R2C2 and R3C1.

I think that's hard to do in a formula, and easy in a UDF if you use ranges rather than literal arrays:

I need a formula solution. And I need the formula to pick up the first occurrence of the needed value in cube one (I wrote a formula that could do the required task but only if cube 1 has only 1 occurrence of the needed value).

1] LOOKUP requires a sorted match range.
2] Fed with a matrix as match area, like you have {0,0,0;0,1,0;1,0,0}, LOOKUP will pick out the last column of that matrix as the match range, not every column.
3] Fed with a matrix as result area, like you have {"R1C1","R1C2","R1C3";"R2C1","R2C2","R2C3";"R3C1","R3C2","R3C3"}, LOOKUP will error out, expecting a vector from which to return a result.

Thank you. This explains why Lookup does not work (this would have been too easy). Index/match also does not work (I guess for the same reason?).

Is there any other formula that might do it?

 

[Aladin Akyurek]

....

1] LOOKUP requires a sorted match range.
2] Fed with a matrix as match area, like you have {0,0,0;0,1,0;1,0,0}, LOOKUP will pick out the last column of that matrix as the match range, not every column.
3] Fed with a matrix as result area, like you have {"R1C1","R1C2","R1C3";"R2C1","R2C2","R2C3";"R3C1","R3C2","R3C3"}, LOOKUP will error out, expecting a vector from which to return a result.

Thank you. This explains why Lookup does not work (this would have been too easy). Index/match also does not work (I guess for the same reason?).

Is there any other formula that might do it?

Index/Match won't work either, indeed, for the same reason. But consider...

<TABLE style="WIDTH: 432pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=576><COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R1C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R1C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R1C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R2C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R2C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R2C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R2C2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R3C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R3C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>R3C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

A2:C4 and E2:G4 houses the relevant data.

I2, control+shift+enter, not just enter:

=MIN(IF(A2:C4=1,ROW(A2:C4)-ROW(A2)+1))

I3, control+shift+enter, not just enter:

=INDEX(E2:G4,I2,MIN(IF(A2:C4=1,
    IF(ROW(A2:C4)-ROW(A2)+1=I2,
     COLUMN(A2:C4)-COLUMN(A2)+1))))

 

[Gene Khalyapin]

Index/Match won't work either, indeed, for the same reason. But consider...

Aladin, this worked great (I combined the two functions). Thank you for your help!

 

[Aladin Akyurek]

Aladin, this worked great (I combined the two functions). Thank you for your help!

Great to hear all that. Thanks for providing feedback.