Hi Excel Community
I am struggling to understand why my Index & Match formula is not picking the correct data in some cells.
As this is a large file with confidential data I have shown the formulae via a PDF and hope someone can spot my error.
Example
Tab A ID 1555 is £120,000 and Tab B it is £120,000, however only £1 is being shown
Similar issue with IDs 42,42 and 48.
ID 1052 works OK.
Any advice please.
Thanks
It could be some format related issue.
Check if cell format is the same across data range in tab B.
HiOriginally Posted by Estevaoba
Format is consistent in both tabs.
Thanks
First, the cell format per se is irrelevant. However, the type of cell value (text v. numeric) might the issue.
Use ISNUMBER or ISTEXT to determine the type of cell value. A visual inspection and cell format can be misleading.
But if that were the problem, I would expect MATCH to fail and return a #VALUE error; thus, INDEX would return a #VALUE error. I don't see how the INDEX/MATCH formula can possibly return 1, based on the values and formula that we see in screen image.
Second, it would behoove you to attach an Excel file, not a PDF. The problem might be subtle and not visible in a screen image.
I understand that the full Excel file contains confidential data. But it should not be difficult to copy-and-paste-value just the data in the relavent columns (columns E and DB in Tab B and column E in Tab A) and the formulas in column AO in Tab A. Be sure that the redacted Excel file still duplicates the problem.
I do notice that while the formula in AO45 has the proper type of cell references (absolute ranges), the formula that you "display" (typed manually?) for AO141 has relative cell references. They will change as you copy the formula down the column.
Even then, again, I do not see how the INDEX/MATCH formula can return 1 and 0, based on what we see in the screen image.
Another thing, make sure values are clean.
Sometimes it’s just a simple space that was misplaced.
Thanks for the answers
Hi
The reason you are not getting the answers you expect is probably because you are not using the correct formula for what you are asking Excel to do.
You need to use the SUMIFS formula.
On sheet [Tab A] try this formula for column [AO], starting in cell [AO2] and copying down..
=SUMIFS('Tab B'!BD:BD,'Tab B'!E:E,E2,'Tab B'!AC:AC,LEFT('Tab A'!$AN$1,4))
When you use INDEX/MATCH, if there are multiple entries for your 'Initiative ID' in column [E], it will return the first match it finds in column [E] on the sheet [Tab B], regardless of the 'Initiative Year' in column [AC] on the [Tab B] sheet.
So, just because you have filtered for the year 2019 in column [AC] on the [Tab B] sheet, your INDEX/MATCH formula is probably finding a previous entry on the [Tab B] sheet for that particular 'Initiative ID' i.e. INDEX/MATCH does not just work with 'visible cells' only.
If you use the SUMIFS formula as above, it is checking that the 'Initiative ID' matches in column [E] , and, in addition, that the year in column [AC] on sheet [Tab B] matches the first-4-digits of the cell [$AN$1] on sheet [Tab A]
I have used entire-column-references e.g. 'Tab B'!BD:BD as this is more efficient (this recognises the 'used-range' etc etc etc)
zeddy
Excel Multi-Sympton Relief Unit
PS: This thread was marked as SOLVED before I posted my answer, so it wasn't my answer that SOLVED it. Just saying.
Last edited by zzzeddy; 11-24-2019 at 04:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks