LinkBack URL
About LinkBacks
The below code isn't quite working, any ideas how this should be worded?
Please Login or Register to view this content.
The below code isn't quite working, any ideas how this should be worded?
Please Login or Register to view this content.
It is probably not liking the statement of C2=Centrac!B2:B1151, one cell = a range, what are you trying to achieve with this formula? Can you post a desensitized sample sheet?
C2=Centrac!B2:B1151 is only going to match the first cell in the range, so that statement is saying the same thing as
C2=Centrac!B2
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
I'm trying to find the cell value within a range.
C2 will be an account number, F2 will be a payment amount.
I am trying to find the payment that matches the account number and the payment amount and then vlookup the payment amount if nboth values are found.
Last edited by Sc0tt1e; 07-28-2014 at 04:03 PM.
Well, I gathered that much from the title and the use of a vlookup. Can you upload an example of what you are trying to do? The IF(AND(C2=Centrac!B2:B1151... is what I am wondering about, as it sits that is not a valid formula, in order to help with the issue I would need to know what that part is there for.
Is it to verify that the value you are looking for is listed on the other page, as to not return an error? If that's the case you can use an IFERROR(
=IFERROR(VLOOKUP('Invoice Data'!F2,Centrac!A2:F1151,6,FALSE),"")
That looks like an ARRAY formula, try entering using CTRL SHIFT enter, not just enter
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
This is not error checking, I am attempting to validate data between two different systems.
On sheet 'Invoice Data' I have the data from the bank postings, on 'Centrac' I have customer account postings. I am attempting to check the account number and payment amount on sheet one against the range of data on sheet 2. If both conditions on sheet 2 are found I want the payment amount (row 6 of the vlookup) to be returned.
If the following conditions are met
then do the followingPlease Login or Register to view this content.
else leave the cell blank.Please Login or Register to view this content.
Last edited by Sc0tt1e; 07-28-2014 at 04:21 PM.
Ok. This might do the trick then.
Formula:
Please Login or Register to view this content.
Stating that a cell = a range is an array method as FDibbins says, I am not sure if that is what you are trying to do, but this formula is non-array.
If I say A1=F5:F1000, it will always return a false unless F5=A1, even if F6 through F1000 are all identical to A1, it will be a false if F5 is not. Unless you array enter. Then it will return an array of {FALSE,TRUE,TRUE,TRUE....} (which is still false if you are looking in only one cell! :P)
If will probably help if you upload a sample of what you have, and show what you want, then we wont have to guess
OK file attached.
I am searching for individual entries from Invoice data tab within the range of data on Centrac tab.
Invoice data column C holds account numbers, column P carries out a Vlookup to see if the account number shows up on the Centrac tab, column B.
In column Q of Invoice data I want a formula that searches Centrac for both the account number and payment amount (Column F) in Centrac, if it finds both on one line then I want the payment value of column F of Centrac for that entry.
Hence the and if and the vlookup for the result.
That formula did not work
Try this in Q2 of the Invoice sheet:
=SUMIFS(Centrac!F:F,Centrac!F:F,F2,Centrac!B:B,C2)
then copy down.
Hope this helps.
Pete
That was the original code we started with but it adds up all the results for the account number (i.e. if there are 4 payments of £5 then £20 shows in all 4 cells in column Q).
I thought that was what you wanted. If you use VLOOKUP then it will only return the first value of any particular matching combination. Maybe you should use AVERAGEIFS instead of SUMIFS.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks