Complicated lookup function

[chrisrowe_cr]

--------------------------------------------------------------------------------

Hi all,

Right, im working between 2 spreadsheets, pulling accross large amounts of data, so a lookup seems the most obvious way forward.

Problem is that I need to look up multiple values. I need to lookup the risk value '2' in range xyz and then find the product in that range with a 'x' rating and return its name. The product name is in col D, the value in col DA and the rating in col CX

If that doesnt make sence in short I need to lookup the number 'x' rated product with a risk value of 'x' in the range xyz and return the products name,

can someone pls help?

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[chrisrowe_cr]

whats the CTRL SHIFT ENTER bit mean?

[chrisrowe_cr]

=INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))

ok thats what it looks like, but it returns a #NAME error

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> --------------------------------------------------------------------------------
>
> Hi all,
>
> Right, im working between 2 spreadsheets, pulling accross large amounts
> of data, so a lookup seems the most obvious way forward.
>
> Problem is that I need to look up multiple values. I need to lookup the
> risk value '2' in range xyz and then find the product in that range with
> a 'x' rating and return its name. The product name is in col D, the
> value in col DA and the rating in col CX
>
> If that doesnt make sence in short I need to lookup the number 'x'
> rated product with a risk value of 'x' in the range xyz and return the
> products name,
>
> can someone pls help?
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>

[Biff]

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr" <[email protected]>
wrote in message
news:[email protected]...
>
> =INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
> plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
> 30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
> Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))
>
> ok thats what it looks like, but it returns a #-NAME- error
>
>
> --
> chrisrowe_cr
> ------------------------------------------------------------------------
> chrisrowe_cr's Profile:
> http://www.excelforum.com/member.php...o&userid=25220
> View this thread: http://www.excelforum.com/showthread...hreadid=388002
>