| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
another lookup brute force question
is it possible to make a lookup_reference and lookup result directly from a
non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
Driller,
Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
thanks Sir Bernie,
i've seen the help file about the match_types 1,0,-1 what does this False mean =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE))<<<<< thanks for any enlightenment 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: Driller, Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
4pinoyjunior,
False = 0, and forces an exact match rather than finding the value before the first value that exceeds the "looked up" number (using a 1) or the converse (using a -1) in a sorted table. HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... thanks Sir Bernie, i've seen the help file about the match_types 1,0,-1 what does this False mean =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE))<<<<< thanks for any enlightenment 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: Driller, Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
Thanks Sir Bernie,
i place it in my worksheet and tested it, yet got minor problem.. Data is A1 = 3 (generated by a formula) W1=U1+V1 Z1=X1+Y1 AC1=AA1+AB1 K1=I1+J1 N1=L1+M1 Q1=O1+P1 iam looking if there is an exact addend "3" on {U1:V1&","&X1:Y1&","&AA1:AB1} to give me the its derivative from addends in {I1:J1&","&L1:M1&","&O1:P1} the problem comes in a coincidence when the value of U1 =1 and V1=2, which gave me a result of 3 located on W1. then the formula result gave me the sum located on K1. i cannot change the structure of the cells, since this is not mySheet. it seem to work when there is no coincidence of match value from the cells on W1 and Z1. any suggestion, please Sir, -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: 4pinoyjunior, False = 0, and forces an exact match rather than finding the value before the first value that exceeds the "looked up" number (using a 1) or the converse (using a -1) in a sorted table. HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... thanks Sir Bernie, i've seen the help file about the match_types 1,0,-1 what does this False mean =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE))<<<<< thanks for any enlightenment 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: Driller, Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
=IF(NOT(ISERROR(MATCH(A1,U1:V1,FALSE))),INDEX(I1:J 1,MATCH(A1,U1:V1,FALSE)),IF(NOT(ISERROR(MATCH(A1,X 1:Y1,FALSE))),INDEX(L1:M1,MATCH(A1,X1:Y1,FALSE)),I F(NOT(ISERROR(MATCH(A1,AA1:AB1,FALSE))),INDEX(O1:P 1,MATCH(A1,AA1:AB1,FALSE)),"Not
Found"))) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... Thanks Sir Bernie, i place it in my worksheet and tested it, yet got minor problem.. Data is A1 = 3 (generated by a formula) W1=U1+V1 Z1=X1+Y1 AC1=AA1+AB1 K1=I1+J1 N1=L1+M1 Q1=O1+P1 iam looking if there is an exact addend "3" on {U1:V1&","&X1:Y1&","&AA1:AB1} to give me the its derivative from addends in {I1:J1&","&L1:M1&","&O1:P1} the problem comes in a coincidence when the value of U1 =1 and V1=2, which gave me a result of 3 located on W1. then the formula result gave me the sum located on K1. i cannot change the structure of the cells, since this is not mySheet. it seem to work when there is no coincidence of match value from the cells on W1 and Z1. any suggestion, please Sir, -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: 4pinoyjunior, False = 0, and forces an exact match rather than finding the value before the first value that exceeds the "looked up" number (using a 1) or the converse (using a -1) in a sorted table. HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... thanks Sir Bernie, i've seen the help file about the match_types 1,0,-1 what does this False mean =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE))<<<<< thanks for any enlightenment 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: Driller, Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
perfect sir,
i never thought i will need an if- long formula. TFH "thanks for help" 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: =IF(NOT(ISERROR(MATCH(A1,U1:V1,FALSE))),INDEX(I1:J 1,MATCH(A1,U1:V1,FALSE)),IF(NOT(ISERROR(MATCH(A1,X 1:Y1,FALSE))),INDEX(L1:M1,MATCH(A1,X1:Y1,FALSE)),I F(NOT(ISERROR(MATCH(A1,AA1:AB1,FALSE))),INDEX(O1:P 1,MATCH(A1,AA1:AB1,FALSE)),"Not Found"))) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... Thanks Sir Bernie, i place it in my worksheet and tested it, yet got minor problem.. Data is A1 = 3 (generated by a formula) W1=U1+V1 Z1=X1+Y1 AC1=AA1+AB1 K1=I1+J1 N1=L1+M1 Q1=O1+P1 iam looking if there is an exact addend "3" on {U1:V1&","&X1:Y1&","&AA1:AB1} to give me the its derivative from addends in {I1:J1&","&L1:M1&","&O1:P1} the problem comes in a coincidence when the value of U1 =1 and V1=2, which gave me a result of 3 located on W1. then the formula result gave me the sum located on K1. i cannot change the structure of the cells, since this is not mySheet. it seem to work when there is no coincidence of match value from the cells on W1 and Z1. any suggestion, please Sir, -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: 4pinoyjunior, False = 0, and forces an exact match rather than finding the value before the first value that exceeds the "looked up" number (using a 1) or the converse (using a -1) in a sorted table. HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... thanks Sir Bernie, i've seen the help file about the match_types 1,0,-1 what does this False mean =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE))<<<<< thanks for any enlightenment 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: Driller, Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
IF you want to use a simple formula, create links to cells U1:AB1 (with the summing cells W1 and Z1
left out) in another range, say I4:P4, and use the formula =INDEX(I1:P1,MATCH(A1,I4:P4,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... perfect sir, i never thought i will need an if- long formula. TFH "thanks for help" 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: =IF(NOT(ISERROR(MATCH(A1,U1:V1,FALSE))),INDEX(I1:J 1,MATCH(A1,U1:V1,FALSE)),IF(NOT(ISERROR(MATCH(A1,X 1:Y1,FALSE))),INDEX(L1:M1,MATCH(A1,X1:Y1,FALSE)),I F(NOT(ISERROR(MATCH(A1,AA1:AB1,FALSE))),INDEX(O1:P 1,MATCH(A1,AA1:AB1,FALSE)),"Not Found"))) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... Thanks Sir Bernie, i place it in my worksheet and tested it, yet got minor problem.. Data is A1 = 3 (generated by a formula) W1=U1+V1 Z1=X1+Y1 AC1=AA1+AB1 K1=I1+J1 N1=L1+M1 Q1=O1+P1 iam looking if there is an exact addend "3" on {U1:V1&","&X1:Y1&","&AA1:AB1} to give me the its derivative from addends in {I1:J1&","&L1:M1&","&O1:P1} the problem comes in a coincidence when the value of U1 =1 and V1=2, which gave me a result of 3 located on W1. then the formula result gave me the sum located on K1. i cannot change the structure of the cells, since this is not mySheet. it seem to work when there is no coincidence of match value from the cells on W1 and Z1. any suggestion, please Sir, -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: 4pinoyjunior, False = 0, and forces an exact match rather than finding the value before the first value that exceeds the "looked up" number (using a 1) or the converse (using a -1) in a sorted table. HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... thanks Sir Bernie, i've seen the help file about the match_types 1,0,-1 what does this False mean =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE))<<<<< thanks for any enlightenment 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: Driller, Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
another lookup brute force question
Sir Bernie,
thanks again in that case, its okey... best regards and more power 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: IF you want to use a simple formula, create links to cells U1:AB1 (with the summing cells W1 and Z1 left out) in another range, say I4:P4, and use the formula =INDEX(I1:P1,MATCH(A1,I4:P4,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... perfect sir, i never thought i will need an if- long formula. TFH "thanks for help" 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: =IF(NOT(ISERROR(MATCH(A1,U1:V1,FALSE))),INDEX(I1:J 1,MATCH(A1,U1:V1,FALSE)),IF(NOT(ISERROR(MATCH(A1,X 1:Y1,FALSE))),INDEX(L1:M1,MATCH(A1,X1:Y1,FALSE)),I F(NOT(ISERROR(MATCH(A1,AA1:AB1,FALSE))),INDEX(O1:P 1,MATCH(A1,AA1:AB1,FALSE)),"Not Found"))) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... Thanks Sir Bernie, i place it in my worksheet and tested it, yet got minor problem.. Data is A1 = 3 (generated by a formula) W1=U1+V1 Z1=X1+Y1 AC1=AA1+AB1 K1=I1+J1 N1=L1+M1 Q1=O1+P1 iam looking if there is an exact addend "3" on {U1:V1&","&X1:Y1&","&AA1:AB1} to give me the its derivative from addends in {I1:J1&","&L1:M1&","&O1:P1} the problem comes in a coincidence when the value of U1 =1 and V1=2, which gave me a result of 3 located on W1. then the formula result gave me the sum located on K1. i cannot change the structure of the cells, since this is not mySheet. it seem to work when there is no coincidence of match value from the cells on W1 and Z1. any suggestion, please Sir, -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: 4pinoyjunior, False = 0, and forces an exact match rather than finding the value before the first value that exceeds the "looked up" number (using a 1) or the converse (using a -1) in a sorted table. HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... thanks Sir Bernie, i've seen the help file about the match_types 1,0,-1 what does this False mean =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE))<<<<< thanks for any enlightenment 4pinoyjunior -- ***** birds of the same feather flock together.. "Bernie Deitrick" wrote: Driller, Something like =INDEX(I1:P1,MATCH(A1,U1:AB1,FALSE)) HTH, Bernie MS Excel MVP "been drilbled to 2007" wrote in message ... is it possible to make a lookup_reference and lookup result directly from a non-adjacent cells,, maybe something like this... =LOOKUP(A1,{U1:V1&","&X1:Y1&","&AA1:AB1},{I1:J1&", "&L1:M1&","&O1:P1}) like if the value on A1 is exactly equal to a value on either U1 or V1, then RESULT will be from L1 or M1, and if not found, it will search again on the next... if not possible, please advice thanks and more power driller |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A question for Match and Lookup | Excel Discussion (Misc queries) | |||
| Lookup problem | Excel Worksheet Functions | |||
| Another way to lookup data | Excel Worksheet Functions | |||
| Lookup function help | Excel Worksheet Functions | |||
| Lookup and Sort Question | Excel Discussion (Misc queries) | |||
Linear Mode
