• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup from data with multiple entries

P

philiphales

New Member
I have a database (bank statement) 7 columns 7500 rows.

On another spreadsheet I want to extract 3 columns of data - say from B; G & F.

The data runs from 2006 to 2017, so for various years the information I need may be from row 7068 to 7695, which is 2016/2017.

I found this - =INDEX($A$12:$G$617,SMALL(IF($G$12:$G$617=$J$12,ROW($G$12:$G$617)),ROW(1:1)),2), which worked when I tested it starting from A1 to G600, but then I moved the data so that it ran from A600 to G1200 and it did not work.

I hate also having to hold ctrl+shift+enter after checking the formula.

Basically, it is 11 years of my bank statement, but I have a number of properties from which (on another spreadsheet) I need to extract the income and expenditure for different properties for the taxman. This other spreadsheet has a tab for each property. Up to now I filter on column G, which has a reference at the beginning as to which property it is, and the rows are coloured for each tax year.
I then from the taxman spreadsheet under say 'date' being col B press = and then move to the filtered spreadsheet and select say A7068 and hit enter. I do the same for G & F. I then move to the next tab re-filter on the next property and repeat. This takes ages and then I have to double check the column totals to see if I am picking up all the data I need.
I am sure I can use a single formula and virtually automate the whole thing.
Your help will be appreciated.
 
It would help if you posted your spreadsheet; however, based on what little information you gave, try substituting your current formula with this one:
=INDEX($A$600:$G$1200,SMALL(IF($G$12:$G$1200=$J$12,ROW($G$12:$G$1200)),ROW(1:1)),2)

If that doesn't work then try substituting with this one:
=INDEX($A$600:$G$1200,SMALL(IF($A$600:$G$1200=$J$12,ROW($A$600:$G$1200)),ROW(1:1)),2)

Don't forget to use CTRL + SHIFT + ENTER as these are array formulas and won't work without CTRL + SHIFT + ENTER.
 
Hi ,

The formula you have as an example is :

=INDEX($A$12:$G$617,SMALL(IF($G$12:$G$617=$J$12,ROW($G$12:$G$617)),ROW(1:1)),2)

The requirements for such a formula to work are :

1. The formula has to be entered using CTRL SHIFT ENTER.

2. The number of rows in all of the highlighted ranges should be equal , though the starting and ending rows can be different ; of course , in almost every case , the starting and ending rows should be the same.

3. The formula you have posted will not return the correct results.

What the SMALL + IF combination is supposed to do is return , one after the other , those row numbers which satisfy the condition specified in the IF function.

The smallest row which satisfies the condition will be returned first , the next smallest row which satisfies the condition will be returned next , and so on till there are no more rows which satisfy the specified condition.

Suppose the smallest row which satisfies the condition is the first row in the data range ; this row will then be row number 12.

Thus , the final output from the formula will be as if we had written the formula as :

=INDEX($A$12:$G$617,12,2)

which will return the value in cell B23. Clearly this is the wrong result.

Thus , the posted formula needs to be corrected as follows :

=INDEX($A$12:$G$617,SMALL(IF($G$12:$G$617=$J$12,ROW($G$12:$G$617) - MIN(ROW($G$12:$G$617)) + 1),ROW(1:1)),2)

where the highlighted addition will ensure that even though the range starts from row 12 , the index will always start from 1 , which is the first row of data.

As a generic formula , this will be :

=INDEX(datarange,SMALL(IF(criteriarange=criterion,ROW(datarange) - MIN(ROW(datarange)) + 1),ROW(1:1)),2)

datarange can now refer to any range anywhere in the worksheet.

Narayan
 
I have a question.
The data is on another workbook and I can replace the (datarange) with the correct address, but where does ROW(1:1) refer - is this the database or the active spreadsheet where I need to display the results?
 
philiphales said:
I have a question.
The data is on another workbook and I can replace the (datarange) with the correct address, but where does ROW(1:1) refer - is this the database or the active spreadsheet where I need to display the results?
Click to expand...
Hi ,

The ROW(1:1) construct is because you want the number to keep incrementing , so that the first result that is returned is the smallest , the next result that is returned is the next smallest , and so on.

If we could write a formula individually for each result , we would write :

=SMALL(datarange , 1)

=SMALL(datarange , 2)

=SMALL(datarange , 3)

Since we cannot do this over a large number of results , we use instead :

=SMALL(datarange , ROW(1:1))

which when we copy down , automatically changes to 2 , 3 , 4 , 5 ,....

Thus , ROW(1:1) will always be the same , since in the first formula cell , you want the smallest result to be returned , and this will be done by using ROW(1:1).

Narayan
 
Hi
So it does not matter that ROW(1:1), does not refer to where the data sits!
That is NOT - ROW('[My Account Latest Update.xlsm]ACCOUNT'!$1:$1); it just needs to be ROW(1:1)
 
You must log in or register to reply here.
Back
Top