Aug 11 2020 05:23 AM
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.
Aug 11 2020 05:56 AM
Aug 11 2020 06:24 AM
@Vikas_Bhardwaj - The formula returns "#N/A"... any thoughts/suggestions?
Aug 11 2020 06:28 AM
Aug 11 2020 06:33 AM
=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.
Aug 11 2020 06:38 AM
Aug 11 2020 06:40 AM
Aug 11 2020 07:51 AM
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
Aug 11 2020 09:43 AM
As variant for this sample
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")