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

%3CLINGO-SUB%20id%3D%22lingo-sub-1581010%22%20slang%3D%22en-US%22%3EPossible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581010%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20specify%20two%20non%20unique%20values%20and%20have%20Excel%20return%20a%20correlating%20third%20value%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20below.%20I%20do%20not%20own%20the%20source%20data%20so%20cannot%20add%20a%20helper%20column%2Fconcatenate%20the%20two%20reference%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_aeung_0-1597148226038.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211753i45E45F38FF56FBD5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22_aeung_0-1597148226038.png%22%20alt%3D%22_aeung_0-1597148226038.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1581010%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581149%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581149%22%20slang%3D%22en-US%22%3Eyou%20can%20use%20%3DVLOOKUP(I4%2CINDIRECT(%22E%22%26amp%3BMATCH(H4%2CD%3AD%2C0)-1%26amp%3B%22%3AF%22%26amp%3BCOUNTA(E%3AE))%2C2%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581243%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581243%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755822%22%20target%3D%22_blank%22%3E%40Vikas_Bhardwaj%3C%2FA%3E%26nbsp%3B-%20The%20formula%20returns%20%22%23N%2FA%22...%20any%20thoughts%2Fsuggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581249%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581249%22%20slang%3D%22en-US%22%3Eis%20data%20is%20same%20if%20same%20than%20we%20use%20this%20if%20example%20is%20different%20than%20other%20formula%20required%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581265%22%20slang%3D%22de-DE%22%3ESubject%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581265%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755067%22%20target%3D%22_blank%22%3E%40_aeung%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSVERWEIS(I4%3BINDIRECT(%22F%22%26amp%3BCOMPARISON(H4%3BD%3AD%3B0)-1%26amp%3B%22%3AE%22%26amp%3BNUMBER2(E%3AE))%3B2%3B0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20only%20way%20the%20formula%20can%20work%20so%20that%20it%20fits%20your%20needs%20...%20the%20truth's%20sake%2C%20here%20is%20a%20sheet%20as%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Beware%20of%20scammers%20posting%20fake%20support%20numbers%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581278%22%20slang%3D%22de-DE%22%3ESubject%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581278%22%20slang%3D%22de-DE%22%3Ea%20small%20error%20in%20it%20have%2C%20when%20I%20choose%20ice%20cream%20and%20butterscotch%20a%20code%20comes%20out.%20But%20if%20I%20use%20the%20formula%20of%20Vikas_Bhardwaj%20%3CBR%20%2F%3E%20When%20I%20use%20these%20codes%20are%20not%20rotated%2C%20ice%20cream%20codes%20become%20pudding%20codes.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581285%22%20slang%3D%22de-DE%22%3ESubject%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581285%22%20slang%3D%22de-DE%22%3Emy%20formula%20%3CBR%20%2F%3E%20%3DVLOOKUP(I4%2CINDIRECT(%22F%22%26amp%3BMATCH(H4%2CD%3AD%2C0)-1%26amp%3B%22%3AE%22%26amp%3BCOUNTA(E%3AE))%2C2%2C0)%20%3CBR%20%2F%3E%20same%20only%20%22F%22%20and%20%22E%22%20a%20reverse%2C%20with%20differing%20results.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581512%22%20slang%3D%22de-DE%22%3ESubject%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581512%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755067%22%20target%3D%22_blank%22%3E%40_aeung%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ENow%20it%20works%20as%20expected%20...%20if%20there%20is%20no%20agreement%20it%20is%20empty%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(D2%3DH4%2CE2%3DI4)%2CF2%2CIF(AND(D2%3DH4%2CE3%3DI4)%2CF3%2CIF(AND(D2%3DH4%2CE4%3DI4)%2CF4%2CIF(AND(D2%3DH4%2CE5%3DI%204)%2CF5%2CIF(AND(D5%3DH4%2CE6%3DI4)%2CF6%2CIF(AND(D5%3DH4%2CE7%3DI4)%2CF7%2CIF(AND(D5%3DH4%2CE8%3DI4)%2CF8%2C%22)))))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Eattached%20is%20the%20test%20file%20with%20the%20nested%20formula%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582205%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20to%20give%20two%20references%20and%20have%20excel%20return%20correlating%20third%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582205%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F755067%22%20target%3D%22_blank%22%3E%40_aeung%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20for%20this%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20665px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211813i375CA45420A7D787%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(INDEX(%24F%242%3A%24F%2412%2C%0A%20%20%20%20%20%20%20%20AGGREGATE(15%2C6%2C%0A%20%20%20%20%20%20%20%20%20%20%201%2F(ROW(%24D%242%3AINDEX(D%3AD%2CCOUNTA(F%3AF)))-ROW(%24D%241)%26gt%3B%3DMATCH(%24H%244%2C%24D%242%3AINDEX(D%3AD%2CCOUNTA(F%3AF))%2C0))%2F%0A%20%20%20%20%20%20%20%20%20%20%20(%24E%242%3AINDEX(E%3AE%2CCOUNTA(F%3AF))%3D%24I%244)*%0A%20%20%20%20%20%20%20%20%20%20%20(ROW(%24F%242%3AINDEX(F%3AF%2CCOUNTA(F%3AF)))-ROW(%24F%241))%2C1)%0A)%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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")