Indes Match fails when using 2 worksheets in same workbook

Occasional Contributor

have now removed thr last vestiges of any hair I had before starting this INDEX/Match formula and need some guidance please as to why I am am getting either NA, Error, or Spill errors.


In its simplist form, I have a workbook with e (actually more) worksheets.

The first worksheet is named Combined and has data that I wish to move (selectively) to a second worksheet called, for example, BT Bankers Trust or HSBC.


I am using Microsoft 365 (Excel).


Each worksheet has exactly the same column heading as in the Combined worksheet.

The data in the Combined worksheet is not necessarily in and order 
(e.g. Account Source or Date, etc.).


I am using the formula that I entered manually which is:



Column A on the Combined worksheet is titled Account Source.

Coulmn A on the BT Bankers Trust, HSBC (all all other worksheets) is ALSO titled Account Source.


What I am trying to do is find all instances of , for example, HSBC on the Combined worksheet and reference the data in the same order of any HSBC on the Combined worksheet and get the data related to each line in the Combined worksheet that matches HSBC in column A of the HSBC workskeet and retrieve the data for columns B, C D etc. in the Combined worksheet and place taht value in the relevant worksheet, e.g. HSBC worksheet.


An example:  Combined worksheet A19 has the value BT Bankers Trust, cell A23 has the value HSBC.  I want the value in cell B23 on the combined worksheet to appear in cell B20 (first line of data) on the HSBC worksheet.  I am expecting the value 3 but get #NA error.  Similarly, I want the value in cell E19 on the Combined worksheet to appear in cell E20 on the BT Bankers Trust worksheet, where I am expecting the value "Personal Interest Received" but get #NA error (after changing the formula to reflect the different worksheet name.


I simply cannot get this formulas to return the desired data in the correct position!


Any help?  


I can supply a sample workbook if desired/required but could not see an option here to attach it.




5 Replies

@DeWayne Bruce With regard to the HSBC example, try it this way in B20 on the HSBC sheet:


The first argument in an INDEX formula should the the range where you want to extract data from, i.e. column B of the Combined sheet. 

The second argument is for the row index, found by matching the value in A20 (presumably "HSBC") in column A on the HSBC sheet. Note that the MATCH function needs end with ,0 to indicate that you want an exact match.

The third argument in the INDEX formula would be for the column reference, but that's not needed in your case.

The above formula can copied towards the right in order to pick-up values from C, D, E etc.


If all of this makes no sense, yes please, upload your workbook (when you have the Drag, Drop,Browse option) or share it via OneDrive, Dropbox or similar (when you have not).



Thank you very much for your rapid response.

Sadly, the solution still fails.  At least I now get data "transferred" - i.e. no error messages BUT the data is totally incorrect for all columns to the right of Column B into which I carefully typed your formula but then changed the INDEX location to columns C,D, E, etc.

The data simply does not match what should be showing on LINE 19.

If I fill down the problem is even more perplexing because it ALWAYS returns the same values as in Line 19 despite changing making the match value relative to the line.

The data source tab (Combined) has multiple lines related to HSBC but with different dates, account numbers, account groups, description, etc. and the formula is pulling data incorrectly.

Again, I can supply a sample workbook if required to assist troubleshooting

@DeWayne Bruce You can share the file via Onedrive or something similar.

Excellent response that pointed me to my basic error.
The ongoing issue of incrrect data transfer has been overcome by giving each transaction a unique transaction number.
The only issue now is how to stop an empty cell on Combined from displaying zero on the transfer page (e.g. HSBC)
Thanks very much for your professional assistance! Well done

@DeWayne Bruce Glad you worked it out. To avoid blanks to return zero, try wrapping the entire formula in an IF statement that starts by checking the content of the referenced cell.


Something like:

=IF( cell="","", the INDEX/MATCH formula )