Can't get Index/Match to work correctly

I have a multisheet Excel workbook where each tab is a different financial account.
To avoid copy/paste multiple ranges from each sheet to a summary worksheet, I am attempting to use Index/Match to cause the data to "automatically" flkow to the summary worksheet.

As an example, I have a worksheet named BT Trust. Column F in that work sheet has a number that relates the the type of transaction in BT Trust. Column H is the debit amounts and coiumn I is the credit amounts.

In the summary worksheet, Column F has the same transaction number.

The formula I am trying to use draws date, but it is incorrect, always pulling the transaction identification number!

=INDEX($F$17:$F37,MATCH($F17,'BT Investors Choice'!$F$17:$F$37),4)

Column 4 is the 4th column to the right of 'BT Investors Choice'!$F$17:$F$37), beoing the debit column

What am I doing incorrectly?

You either need a 4 column range as the first argument to INDEX:

=INDEX($F$17:$I$37,MATCH($F17,'BT Investors Choice'!$F$17:$F$37),4)

or just use the column you want the value back from:

=INDEX($I$17:$I$37,MATCH($F17,'BT Investors Choice'!$F$17:$F$37))

Note: your MATCH function assumes that the data on the Investors sheet is sorted in ascending order, and will match the last value less than or equal to the lookup value. If you wanted an exact match, you should add the third argument to match as 0.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

I will provide the link for you this time: https://www.excelforum.com/excel-formulas-and-functions/1376738-unable-to-get-index-match-to-make-data-from-one-worksheet-to-to-a-summary-worksheet.html

No joy!
I am replying with a sample workbook of 2 work sheets, annotated with the data, comments about the aim and the two formulae you have kindly provided, each showing an error. If I can get the sample workbook to function correctly I can adapt the formule to fit ther dats that I wish to use.

Thanks for the support so far.

Help

AliGW said:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

I will provide the link for you this time: https://www.excelforum.com/excel-formulas-and-functions/1376738-unable-to-get-index-match-to-make-data-from-one-worksheet-to-to-a-summary-worksheet.html

Click to expand...

It does not matter which Forum I try to access, it ALWAYS brings me back to THOIS one with you message about cross-site posting! WHY?

No, it doesn't. The link above is to ExcelForum. Here it is again:

https://www.excelforum.com/excel-fo...et-to-to-a-summary-worksheet.html#post5667687

On ExcelForum we are waiting for you to provide a link to this thread.

ExcelGuru and Excel Forum are not the same, but I happen to be a moderator on both.

Attached is the solution I've offered you over on ExcelForum (posting here for the benefit of members here).

Here is a copy that does the whole lot for you using this in cell A12:

=FILTER(Combined!$A$5:$D$14,Combined!$A$5:$A$14="BT Investors Choice")

The bit in red could just as easily be a cell reference on the summary sheet, e.g.

A1="BT Investors Choice" (without the speech marks) and in A18:

=FILTER(Combined!$A$5:$D$14,Combined!$A$5:$A$14=A1)