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

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. 8 Replies

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

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

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

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

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

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

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

=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.

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

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.

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

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.

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

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

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

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")``````