Possible to give two references and have excel return correlating third value?

Copper Contributor

Is it possible to specify two non unique values and have Excel return a correlating third value? 

 

Example below. I do not own the source data so cannot add a helper column/concatenate the two reference columns. 

 

_aeung_0-1597148226038.png

 

8 Replies
you can use =VLOOKUP(I4,INDIRECT("E"&MATCH(H4,D:D,0)-1&":F"&COUNTA(E:E)),2,0)

@Vikas_Bhardwaj - The formula returns "#N/A"... any thoughts/suggestions?

is data is same if same than we use this if example is different than other formula required

@_aeung 

 

=SVERWEIS(I4;INDIREKT("F"&VERGLEICH(H4;D:D;0)-1&":E"&ANZAHL2(E:E));2;0)

 

This is the only way the formula can work so that it fits your needs ... the truth’s sake, here is a sheet as an example.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

* Beware of scammers posting fake support numbers here.

a small error in it have, when I choose ice cream and butterscotch a code comes out. But if I use the formula of Vikas_Bhardwaj
When I use these the codes are not rotated, ice cream codes become pudding codes.

my formula in english
=VLOOKUP(I4,INDIRECT("F"&MATCH(H4,D:D,0)-1&":E"&COUNTA(E:E)),2,0)
same only "F" and "E" a reverse, with differend results.

@_aeung 

Now it works as expected ... if there is no agreement it is empty

 

=IF(AND(D2=H4,E2=I4),F2,IF(AND(D2=H4,E3=I4),F3,IF(AND(D2=H4,E4=I4),F4,IF(AND(D2=H4,E5=I4),F5,IF(AND(D5=H4,E6=I4),F6,IF(AND(D5=H4,E7=I4),F7,IF(AND(D5=H4,E8=I4),F8,"")))))))

 

attached is the test file with the nested formula

@_aeung 

As variant for this sample

image.png

that could be

=IFNA(INDEX($F$2:$F$12,
        AGGREGATE(15,6,
           1/(ROW($D$2:INDEX(D:D,COUNTA(F:F)))-ROW($D$1)>=MATCH($H$4,$D$2:INDEX(D:D,COUNTA(F:F)),0))/
           ($E$2:INDEX(E:E,COUNTA(F:F))=$I$4)*
           (ROW($F$2:INDEX(F:F,COUNTA(F:F)))-ROW($F$1)),1)
),"no such")