| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Simple array compare not working
Hello, maybe someone can see something obvious here.
I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple array compare not working
You say it is part of a MATCH function - can you post a copy of the complete
formula that you are using? Pete "PeteJ" wrote in message ... Hello, maybe someone can see something obvious here. I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple array compare not working
Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with
multiple columns to compare without editing the original file. I got this hint from the discussion boards, and am trying to apply it. {=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) * ('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))} In the Match, I am multiplying 3 results to see if they all return "TRUE". But they are all returning "FALSE". Actually, the whole formula returns #NUM!, but stepping through it's because the array compares are returning "FALSE" all the time. The following works just fine: =VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE) But of course only takes into consideration the A2 cell. "Pete_UK" wrote: You say it is part of a MATCH function - can you post a copy of the complete formula that you are using? Pete "PeteJ" wrote in message ... Hello, maybe someone can see something obvious here. I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple array compare not working
Unless you're using xl2007, you can't use the whole column in your array
formula. (You are using ctrl-shift-enter to enter the formula in the cell, right?) And you may want to limit the range to what you expect (plus a little bit more). I've seen some formulas that try to use lots of rows (or complete columns) cause excel to yell about not being able to complete the task with available resources. (That may be when the "sending" workbook is closed, though.) PeteJ wrote: Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with multiple columns to compare without editing the original file. I got this hint from the discussion boards, and am trying to apply it. {=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) * ('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))} In the Match, I am multiplying 3 results to see if they all return "TRUE". But they are all returning "FALSE". Actually, the whole formula returns #NUM!, but stepping through it's because the array compares are returning "FALSE" all the time. The following works just fine: =VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE) But of course only takes into consideration the A2 cell. "Pete_UK" wrote: You say it is part of a MATCH function - can you post a copy of the complete formula that you are using? Pete "PeteJ" wrote in message ... Hello, maybe someone can see something obvious here. I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple array compare not working
To the best of my knowledge, you cannot use whole column references in array
formulas... try $H1:$Hnnn (where nnn is the largest numbered row you will need... I'm told it is more efficient to restrict the size of the range where possible), $A1:$Annn, etc. Rick "PeteJ" wrote in message ... Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with multiple columns to compare without editing the original file. I got this hint from the discussion boards, and am trying to apply it. {=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) * ('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))} In the Match, I am multiplying 3 results to see if they all return "TRUE". But they are all returning "FALSE". Actually, the whole formula returns #NUM!, but stepping through it's because the array compares are returning "FALSE" all the time. The following works just fine: =VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE) But of course only takes into consideration the A2 cell. "Pete_UK" wrote: You say it is part of a MATCH function - can you post a copy of the complete formula that you are using? Pete "PeteJ" wrote in message ... Hello, maybe someone can see something obvious here. I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple array compare not working
Thanks. I've changed the reference to be limited, and the result does change,
but I now get back #N/A instead of #NUM!. I've tried to simplify the formula as much as possible to debug it. It's now: {=INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0))} Yes, entered with ctrl-shift-enter. Stepping through the calculation, it gets the value of A2 correctly, then expands out E2:E20 correctly with the values from those cells. It then changes this to a list of values of "False" and "True", with the "True" value being 3rd in the list, properly cooresponding the the 3rd value in the list that matches. This is where things go wrong, with the next evaluation changing the whole MATCH statement to "#N/A". Here's the evaluation right befo = INDEX($I$2:$I:$20, MATCH(1,{FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE},0)) Any ideas? "Rick Rothstein (MVP - VB)" wrote: To the best of my knowledge, you cannot use whole column references in array formulas... try $H1:$Hnnn (where nnn is the largest numbered row you will need... I'm told it is more efficient to restrict the size of the range where possible), $A1:$Annn, etc. Rick "PeteJ" wrote in message ... Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with multiple columns to compare without editing the original file. I got this hint from the discussion boards, and am trying to apply it. {=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) * ('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))} In the Match, I am multiplying 3 results to see if they all return "TRUE". But they are all returning "FALSE". Actually, the whole formula returns #NUM!, but stepping through it's because the array compares are returning "FALSE" all the time. The following works just fine: =VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE) But of course only takes into consideration the A2 cell. "Pete_UK" wrote: You say it is part of a MATCH function - can you post a copy of the complete formula that you are using? Pete "PeteJ" wrote in message ... Hello, maybe someone can see something obvious here. I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple array compare not working
This array formula:
=INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0)) should be: =INDEX(I2:I20, MATCH(True, (E2:E20=A2), 0)) With your other formula, you're multiplying true/falses to get 1's and 0's. PeteJ wrote: Thanks. I've changed the reference to be limited, and the result does change, but I now get back #N/A instead of #NUM!. I've tried to simplify the formula as much as possible to debug it. It's now: {=INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0))} Yes, entered with ctrl-shift-enter. Stepping through the calculation, it gets the value of A2 correctly, then expands out E2:E20 correctly with the values from those cells. It then changes this to a list of values of "False" and "True", with the "True" value being 3rd in the list, properly cooresponding the the 3rd value in the list that matches. This is where things go wrong, with the next evaluation changing the whole MATCH statement to "#N/A". Here's the evaluation right befo = INDEX($I$2:$I:$20, MATCH(1,{FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE},0)) Any ideas? "Rick Rothstein (MVP - VB)" wrote: To the best of my knowledge, you cannot use whole column references in array formulas... try $H1:$Hnnn (where nnn is the largest numbered row you will need... I'm told it is more efficient to restrict the size of the range where possible), $A1:$Annn, etc. Rick "PeteJ" wrote in message ... Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with multiple columns to compare without editing the original file. I got this hint from the discussion boards, and am trying to apply it. {=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) * ('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))} In the Match, I am multiplying 3 results to see if they all return "TRUE". But they are all returning "FALSE". Actually, the whole formula returns #NUM!, but stepping through it's because the array compares are returning "FALSE" all the time. The following works just fine: =VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE) But of course only takes into consideration the A2 cell. "Pete_UK" wrote: You say it is part of a MATCH function - can you post a copy of the complete formula that you are using? Pete "PeteJ" wrote in message ... Hello, maybe someone can see something obvious here. I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Simple array compare not working
Awesome, thanks. I had taken that formula directly from another post, but it
does make sense. - Pete "Dave Peterson" wrote: This array formula: =INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0)) should be: =INDEX(I2:I20, MATCH(True, (E2:E20=A2), 0)) With your other formula, you're multiplying true/falses to get 1's and 0's. PeteJ wrote: Thanks. I've changed the reference to be limited, and the result does change, but I now get back #N/A instead of #NUM!. I've tried to simplify the formula as much as possible to debug it. It's now: {=INDEX(I2:I20, MATCH(1, (E2:E20=A2), 0))} Yes, entered with ctrl-shift-enter. Stepping through the calculation, it gets the value of A2 correctly, then expands out E2:E20 correctly with the values from those cells. It then changes this to a list of values of "False" and "True", with the "True" value being 3rd in the list, properly cooresponding the the 3rd value in the list that matches. This is where things go wrong, with the next evaluation changing the whole MATCH statement to "#N/A". Here's the evaluation right befo = INDEX($I$2:$I:$20, MATCH(1,{FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE},0)) Any ideas? "Rick Rothstein (MVP - VB)" wrote: To the best of my knowledge, you cannot use whole column references in array formulas... try $H1:$Hnnn (where nnn is the largest numbered row you will need... I'm told it is more efficient to restrict the size of the range where possible), $A1:$Annn, etc. Rick "PeteJ" wrote in message ... Sure, here it is. I'm trying to figure out a way to do a VLOOKUP with multiple columns to compare without editing the original file. I got this hint from the discussion boards, and am trying to apply it. {=INDEX('[File1.xls]SW'!$H:$H, MATCH(1, ('[File1.xls]SW'!$A:$A = A2) * ('[File1.xls]SW'!$F:$F = "AMT") * ('[File1.xls]SW'!$G:$G = "ICD"), 0))} In the Match, I am multiplying 3 results to see if they all return "TRUE". But they are all returning "FALSE". Actually, the whole formula returns #NUM!, but stepping through it's because the array compares are returning "FALSE" all the time. The following works just fine: =VLOOKUP(A2, '[File1.xls]SW!$A:$H, 7, FALSE) But of course only takes into consideration the A2 cell. "Pete_UK" wrote: You say it is part of a MATCH function - can you post a copy of the complete formula that you are using? Pete "PeteJ" wrote in message ... Hello, maybe someone can see something obvious here. I've got the following compare as part of a MATCH function, and it always returns false. It doesn't seem to be stepping past the first row of the column A. I am entering this as an array formula (ctrl-shift-enter). How come it's not going past the first row? {=('[File1.xls]SW'!A2:A100=A2)} I get the same behavior using {=('[File1.xls]SW'!$A:$A=A2)} There is definately a value in column A that matches A2 of this sheet. It works with a VLOOKUP. Thanks, - Pete -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple Array | Excel Discussion (Misc queries) | |||
| Compare Two Simple Workbooks & Remove Duplicate Rows | Excel Discussion (Misc queries) | |||
| simple formlas not working | New Users to Excel | |||
| This should be simple but it's not working for me! | New Users to Excel | |||
| Is this simple I'm trying to compare values from 4 excel tables | Excel Worksheet Functions | |||
Linear Mode
