I'm trying to return one value from col C given cols A & B. Data looks like:
A B C
11 isin IDXXXXXXX
11 ric XXX.XX
11 sedol XXXXXX1
11 ticker XXXX
22 isin IDXXXXXXXX
22 ric XXX.XX
22 sedol XXXXXX2
etc. etc.

I can't manipulate the original data and must find col C given col A's value.

I hope someone can help
thanks
--
yeah wiwl

What value are you trying to return from column C? IT's not clear from your
question.

Barb Reinhardt



"YeahWiwl123" wrote:

I'm trying to return one value from col C given cols A & B. Data looks like:
A B C
11 isin IDXXXXXXX
11 ric XXX.XX
11 sedol XXXXXX1
11 ticker XXXX
22 isin IDXXXXXXXX
22 ric XXX.XX
22 sedol XXXXXX2
etc. etc.

I can't manipulate the original data and must find col C given col A's value.

I hope someone can help
thanks
--
yeah wiwl

I would like to return the "ric" in col C for each col A value,

thanks
--
yeah wiwl


"Barb Reinhardt" wrote:

What value are you trying to return from column C? IT's not clear from your
question.

Barb Reinhardt



"YeahWiwl123" wrote:

I'm trying to return one value from col C given cols A & B. Data looks like:
A B C
11 isin IDXXXXXXX
11 ric XXX.XX
11 sedol XXXXXX1
11 ticker XXXX
22 isin IDXXXXXXXX
22 ric XXX.XX
22 sedol XXXXXX2
etc. etc.

I can't manipulate the original data and must find col C given col A's value.

I hope someone can help
thanks
--
yeah wiwl

try:

=INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=E1)*($B$1: $B$100="ric"),0))

Enter with Ctrl+Shift+Enter. You will get {} brackets round formula if
entered correctly

E1=11

HTH

"YeahWiwl123" wrote:

I would like to return the "ric" in col C for each col A value,

thanks
--
yeah wiwl


"Barb Reinhardt" wrote:

What value are you trying to return from column C? IT's not clear from your
question.

Barb Reinhardt



"YeahWiwl123" wrote:

I'm trying to return one value from col C given cols A & B. Data looks like:
A B C
11 isin IDXXXXXXX
11 ric XXX.XX
11 sedol XXXXXX1
11 ticker XXXX
22 isin IDXXXXXXXX
22 ric XXX.XX
22 sedol XXXXXX2
etc. etc.

I can't manipulate the original data and must find col C given col A's value.

I hope someone can help
thanks
--
yeah wiwl

I'm still confused but I guess that's a function of my age but how about this
for a guess:-

Looking for 11 in colimn A and Ric in Column B and returning column C does
this work?

=INDEX(C1:C10,MATCH(11&"ric",A1:A10&B1:B10,0))

It's an array so enter with Ctrl+Shift+Enter.

Mike

"YeahWiwl123" wrote:

I would like to return the "ric" in col C for each col A value,

thanks
--
yeah wiwl


"Barb Reinhardt" wrote:

What value are you trying to return from column C? IT's not clear from your
question.

Barb Reinhardt



"YeahWiwl123" wrote:

I'm trying to return one value from col C given cols A & B. Data looks like:
A B C
11 isin IDXXXXXXX
11 ric XXX.XX
11 sedol XXXXXX1
11 ticker XXXX
22 isin IDXXXXXXXX
22 ric XXX.XX
22 sedol XXXXXX2
etc. etc.

I can't manipulate the original data and must find col C given col A's value.

I hope someone can help
thanks
--
yeah wiwl

Thanks Mike,
Your function works fine for the first 4 lines of data. However when I sub a
cell ref for "11" in the MATCH formula ,in this case A1, and then
Ctrl+Shift+Enter for the array this cell ref does not change for each row's
formula going down (A1, A2, and so on). If I copy and paste the formula one
row at a time and re-align this I could get the formula I need however I have
over 5000 lines of data. Is there a better way?
Bearing in mind when I Ctrl+Enter the formula it returns "#VALUE!",
kind regards
--
yeah wiwl


"Mike H" wrote:

I'm still confused but I guess that's a function of my age but how about this
for a guess:-

Looking for 11 in colimn A and Ric in Column B and returning column C does
this work?

=INDEX(C1:C10,MATCH(11&"ric",A1:A10&B1:B10,0))

It's an array so enter with Ctrl+Shift+Enter.

Mike

"YeahWiwl123" wrote:

I would like to return the "ric" in col C for each col A value,

thanks
--
yeah wiwl


"Barb Reinhardt" wrote:

What value are you trying to return from column C? IT's not clear from your
question.

Barb Reinhardt



"YeahWiwl123" wrote:

I'm trying to return one value from col C given cols A & B. Data looks like:
A B C
11 isin IDXXXXXXX
11 ric XXX.XX
11 sedol XXXXXX1
11 ticker XXXX
22 isin IDXXXXXXXX
22 ric XXX.XX
22 sedol XXXXXX2
etc. etc.

I can't manipulate the original data and must find col C given col A's value.

I hope someone can help
thanks
--
yeah wiwl