I'm having what seems to be an inexplicable inconsistency between two formulas in Excel. I'm attempting to create a multiple output XLOOKUP-type search using this temporary formula:
=INDEX($B$3:$B$41, SMALL(IF(D$2=$A$3:$A$41, ROW($A$3:$A$41)- MIN(ROW($A$3:$A$41))+1,""), ROW()-2))
I'm drawing from simplified data in two columns; A3:A41 including numerical job numbers, and B3:B41 with comments on each job. Each line of comment for a job has a duplicated job number, e.g.
| Job No | Comments |
|---|---|
| 1000 | Comment 1 |
| 1000 | Comment 2 |
| 2000 | Comment 1 |
| 3000 | Comment 1 |
My output is supposed to list all of the comments in a column, headed by the job number, like so:
| 1000 | 2000 | 3000 |
|---|---|---|
| Comment 1 | Comment 1 | Comment 1 |
| Comment 2 |
This works for the first test job (number 1000, in cell D2), and lists all the comments for that job in cells D3:D7, and then lists #NUM! errors for the rest of the pre-set range when it runs out of comments to list, as it should. However, job 2000 in cell E2 results in #NUM! errors for all cells, and isn't finding any comments. The formula is exactly the same (I dragged to fill it), and when I manually change the IF reference for the 2000 job to refer to cell D2, it works.
All of the job numbers are text cells, but the only difference between the successful and unsuccessful formula is the change from ...IF(D$2=$A$3:$A$41... to ...IF(E$2=$A$3:$A$41..., which changes the reference from 1000 to 2000, both of which I've manually confirmed exist and have comments in the test data.
I'm stumped. There's no technical difference between
=INDEX($B$3:$B$41, SMALL(IF(D$2=$A$3:$A$41, ROW($A$3:$A$41)- MIN(ROW($A$3:$A$41))+1,""), ROW()-2))
and
=INDEX($B$3:$B$41, SMALL(IF(E$2=$A$3:$A$41, ROW($A$3:$A$41)- MIN(ROW($A$3:$A$41))+1,""), ROW()-2))
if both D2 and E2 are text cells, is there?