Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
chrisrowe_cr
 
Posts: n/a
Default Complicated lookup function


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

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

  #2   Report Post  
Biff
 
Posts: n/a
Default

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"
wrote in message
...

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

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



  #3   Report Post  
chrisrowe_cr
 
Posts: n/a
Default


whats the CTRL SHIFT ENTER bit mean?


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

  #4   Report Post  
chrisrowe_cr
 
Posts: n/a
Default


=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

  #5   Report Post  
Biff
 
Posts: n/a
Default

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"
wrote in message
news:chrisrowe_cr.1seyii_1121771232.2128@excelforu m-nospam.com...

=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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
lookup function 1 Colboyfx Excel Worksheet Functions 4 July 15th 05 09:15 AM
Lookup function returning reference, not value Caligula Excel Worksheet Functions 1 May 28th 05 06:35 PM
Lookup Function Error Jacinthe Excel Worksheet Functions 2 March 10th 05 08:37 AM
Lookup Function Problem seve Excel Discussion (Misc queries) 9 February 28th 05 03:46 AM
Lookup function w/Text and Year Josh O. Excel Worksheet Functions 1 February 13th 05 12:27 AM


All times are GMT +1. The time now is 11:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"