Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup function 1 | Excel Worksheet Functions | |||
Lookup function returning reference, not value | Excel Worksheet Functions | |||
Lookup Function Error | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions |