Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Comparing Arrays
Hi there,
I am trying to compare two fixed arrays and each of the two parts of the formula seem to return the correct values, but the equation itself doesn't seem to work as desired: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1","F1","G1","H1","J1"}) when pressing F9 while in formula bar it looks like this: ={"d";"d";"d";"d";"d"}={"d","e","d","d","d"} but the final result is: ={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!, #VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!, #VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!,#V ALUE!} (looks like each element in one array is compared to each element in the other) which yields =TRUE (???) If I use SUMPRODUCT: =SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1","F1","G1","H1","J1"}))) the interim results are the same, only the final result is =#VALUE! Both formulas work identically whether array-entered or not. What I am trying to do is to compare the two arrays in this way: d=d=TRUE d=e=FALSE d=d=TRUE d=d=TRUE d=d=TRUE =FALSE Any ideas please? Thanks, KL |
#3
|
|||
|
|||
Bernie,
Thank you for your observation. Unfortunatelly it is just my oversight when putting the formula in this post as I was playing around with the verticality and horizontality of the arrays and apparently have copied the incorrect version in the rush. But you are right - correcting it fixes the number of results. However, those results are still returning errors. Thanks again, hope Harlan, Aladin or someone else could jump in later. KL "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... KL, Well, one problem is that you are comparing a vertical array with a horizontal array. Try using commas instead of semicolons in your first array. That takes care of the large resultant array, and when you F( your way through, it works better. But I don't have any luck array entering the INDIRECT part into multiple cells, so there may be some problem using that in an array formula. But Harlan Grove will weigh in soon, to correct any mistakes I make here <vbg Other than that, I can't help you. Bernie MS Excel MVP "KL" <lapink2000(at)hotmail.com (former ) wrote in message ... Hi there, I am trying to compare two fixed arrays and each of the two parts of the formula seem to return the correct values, but the equation itself doesn't seem to work as desired: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1","F1","G1","H1","J1"}) when pressing F9 while in formula bar it looks like this: ={"d";"d";"d";"d";"d"}={"d","e","d","d","d"} but the final result is: ={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!, #VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!, #VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!,#V ALUE!} (looks like each element in one array is compared to each element in the other) which yields =TRUE (???) If I use SUMPRODUCT: =SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1","F1","G1","H1","J1"}))) the interim results are the same, only the final result is =#VALUE! Both formulas work identically whether array-entered or not. What I am trying to do is to compare the two arrays in this way: d=d=TRUE d=e=FALSE d=d=TRUE d=d=TRUE d=d=TRUE =FALSE Any ideas please? Thanks, KL |
#4
|
|||
|
|||
Hi!
Can't get indirect to generate an array in that form. Not the solution you were looking for but you could always do the monster formula like this: =AND(VLOOKUP(B1,Sheet2!C1:J10,5,0)=F1,VLOOKUP(B1,S heet2! C1:J10,6,0)=G1,....etc,etc) Since B1 will always equal B1 can't you just eliminate that from the formula? Biff -----Original Message----- Bernie, Thank you for your observation. Unfortunatelly it is just my oversight when putting the formula in this post as I was playing around with the verticality and horizontality of the arrays and apparently have copied the incorrect version in the rush. But you are right - correcting it fixes the number of results. However, those results are still returning errors. Thanks again, hope Harlan, Aladin or someone else could jump in later. KL "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... KL, Well, one problem is that you are comparing a vertical array with a horizontal array. Try using commas instead of semicolons in your first array. That takes care of the large resultant array, and when you F( your way through, it works better. But I don't have any luck array entering the INDIRECT part into multiple cells, so there may be some problem using that in an array formula. But Harlan Grove will weigh in soon, to correct any mistakes I make here <vbg Other than that, I can't help you. Bernie MS Excel MVP "KL" <lapink2000(at)hotmail.com (former ) wrote in message ... Hi there, I am trying to compare two fixed arrays and each of the two parts of the formula seem to return the correct values, but the equation itself doesn't seem to work as desired: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE) =INDIRECT({"B1","F1","G1","H1","J1"}) when pressing F9 while in formula bar it looks like this: ={"d";"d";"d";"d";"d"}={"d","e","d","d","d"} but the final result is: ={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!, #VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!, #VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!,#V ALUE!} (looks like each element in one array is compared to each element in the other) which yields =TRUE (???) If I use SUMPRODUCT: =SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10, {1;5;6;7;8},FALSE)=INDIRECT({"B1","F1","G1","H1"," J1"}))) the interim results are the same, only the final result is =#VALUE! Both formulas work identically whether array-entered or not. What I am trying to do is to compare the two arrays in this way: d=d=TRUE d=e=FALSE d=d=TRUE d=d=TRUE d=d=TRUE =FALSE Any ideas please? Thanks, KL . |
#5
|
|||
|
|||
KL wrote...
I am trying to compare two fixed arrays and each of the two parts of the formula seem to return the correct values, but the equation itself doesn't seem to work as desired: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE ) =INDIRECT({"B1","F1","G1","H1","J1"}) when pressing F9 while in formula bar it looks like this: ={"d";"d";"d";"d";"d"}={"d","e","d","d","d"} but the final result is: ={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!} ... You've already been told to use a horizontal array of column indices as 3rd argument to VLOOKUP, so {1,5,6,7,8}. Alternatively, you could use a vertical array of textrefs to INDIRECT, so {"B1";"F1";"G1";"H1";"J1"}. Either will make the two resulting array the same shape/orientation, and will reduce the result of the comparison to either a simple horizontal or veritcal array. As for the #VALUE! results, INDIRECT when passed an array first argument returns somehting that works like an array of range references. In some situations those will appear to work as operands, in other situations (such as array formulas) they won't. Since it appears these cells will evaluate to text, wrap the INDIRECT call inside T(). So try the formula =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =T(INDIRECT({"B1";"F1";"G1";"H1";"J1"})) --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! |
#6
|
|||
|
|||
hrlngrv,
Thanks a bunch - that works. Now out of curiosity: what should I be doing if I have numbers or a mix of numbers and text? Apreciate your help, KL "hrlngrv - ExcelForums.com" wrote in message ... KL wrote... I am trying to compare two fixed arrays and each of the two parts of the formula seem to return the correct values, but the equation itself doesn't seem to work as desired: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALS E) =INDIRECT({"B1","F1","G1","H1","J1"}) when pressing F9 while in formula bar it looks like this: ={"d";"d";"d";"d";"d"}={"d","e","d","d","d"} but the final result is: ={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!; #VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!} .. You've already been told to use a horizontal array of column indices as 3rd argument to VLOOKUP, so {1,5,6,7,8}. Alternatively, you could use a vertical array of textrefs to INDIRECT, so {"B1";"F1";"G1";"H1";"J1"}. Either will make the two resulting array the same shape/orientation, and will reduce the result of the comparison to either a simple horizontal or veritcal array. As for the #VALUE! results, INDIRECT when passed an array first argument returns somehting that works like an array of range references. In some situations those will appear to work as operands, in other situations (such as array formulas) they won't. Since it appears these cells will evaluate to text, wrap the INDIRECT call inside T(). So try the formula =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =T(INDIRECT({"B1";"F1";"G1";"H1";"J1"})) --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! |
#7
|
|||
|
|||
"KL" wrote...
Thanks a bunch - that works. Now out of curiosity: what should I be doing if I have numbers or a mix of numbers and text? .... =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =T(INDIRECT({"B1";"F1";"G1";"H1";"J1"})) If there's an unpredictable mix of text and numbers, the most robust way to handle that would be =(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =T(INDIRECT({"B1";"F1";"G1";"H1";"J1"}))) +(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =N(INDIRECT({"B1";"F1";"G1";"H1";"J1"}))) which will return 1s instead of TRUEs and 0s instead of FALSEs. |
#8
|
|||
|
|||
Twice I've tried to post my reply and both times it seems I was
unsuccessful. Here goes a third time, hopefully without any problems... :) Try the following array formula that needs to be block-entered: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0)=CHOOS E({1;2;3;4;5},B1,F1,G1,H1,J1) Or to have the formula return TRUE or FALSE, depeding on whether each value in the first array equals the second array... =SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0)=CHOOS E({1;2;3;4;5},B1,F1,G1,H1,J1)))=5 Hope this helps! In article , "KL" <lapink2000(at)hotmail.com (former ) wrote: Hi there, I am trying to compare two fixed arrays and each of the two parts of the formula seem to return the correct values, but the equation itself doesn't seem to work as desired: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1","F1","G1","H1" ,"J1"}) when pressing F9 while in formula bar it looks like this: ={"d";"d";"d";"d";"d"}={"d","e","d","d","d"} but the final result is: ={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!, #VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!, #VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!,#V ALUE!} (looks like each element in one array is compared to each element in the other) which yields =TRUE (???) If I use SUMPRODUCT: =SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1", "F1","G1","H1","J1"}))) the interim results are the same, only the final result is =#VALUE! Both formulas work identically whether array-entered or not. What I am trying to do is to compare the two arrays in this way: d=d=TRUE d=e=FALSE d=d=TRUE d=d=TRUE d=d=TRUE =FALSE Any ideas please? Thanks, KL |
#9
|
|||
|
|||
Harlan,
Thanks a lot - works like charm. KL "Harlan Grove" wrote in message ... "KL" wrote... Thanks a bunch - that works. Now out of curiosity: what should I be doing if I have numbers or a mix of numbers and text? ... =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =T(INDIRECT({"B1";"F1";"G1";"H1";"J1"})) If there's an unpredictable mix of text and numbers, the most robust way to handle that would be =(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =T(INDIRECT({"B1";"F1";"G1";"H1";"J1"}))) +(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0) =N(INDIRECT({"B1";"F1";"G1";"H1";"J1"}))) which will return 1s instead of TRUEs and 0s instead of FALSEs. |
#10
|
|||
|
|||
Domenic,
This one is great! Both work without array-entering (I don't need to see the values to manipulate them). Apart from that the second formula is relatively short it also allows to use the cell references dynamically. Cheers, KL "Domenic" wrote in message ... Twice I've tried to post my reply and both times it seems I was unsuccessful. Here goes a third time, hopefully without any problems... :) Try the following array formula that needs to be block-entered: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0)=CHOOS E({1;2;3;4;5},B1,F1,G1,H1,J1) Or to have the formula return TRUE or FALSE, depeding on whether each value in the first array equals the second array... =SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},0)=CHOOS E({1;2;3;4;5},B1,F1,G1,H1,J1)))=5 Hope this helps! In article , "KL" <lapink2000(at)hotmail.com (former ) wrote: Hi there, I am trying to compare two fixed arrays and each of the two parts of the formula seem to return the correct values, but the equation itself doesn't seem to work as desired: =VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1","F1","G1","H1" ,"J1"}) when pressing F9 while in formula bar it looks like this: ={"d";"d";"d";"d";"d"}={"d","e","d","d","d"} but the final result is: ={#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!, #VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!, #VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!,#V ALUE!} (looks like each element in one array is compared to each element in the other) which yields =TRUE (???) If I use SUMPRODUCT: =SUMPRODUCT(--(VLOOKUP(B1,Sheet2!$C$1:$J$10,{1;5;6;7;8},FALSE)=I NDIRECT({"B1", "F1","G1","H1","J1"}))) the interim results are the same, only the final result is =#VALUE! Both formulas work identically whether array-entered or not. What I am trying to do is to compare the two arrays in this way: d=d=TRUE d=e=FALSE d=d=TRUE d=d=TRUE d=d=TRUE =FALSE Any ideas please? Thanks, KL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Excel Horizontal arrays with regional options using. | Excel Discussion (Misc queries) | |||
Comparing Workbook contents | Excel Discussion (Misc queries) | |||
Comparing charts dynamically | Charts and Charting in Excel | |||
Comparing Date Fields | Excel Worksheet Functions | |||
Comparing Data in two columns | Excel Worksheet Functions |