Index Match formula not giving correct answers -- I think I'm missing a piece of the formula

0
votes

I'm trying to use Index Match to Compare a numeric code in Column B with anything that's a match in Column E, and if it's a match -- pull the info from same line in Column D and place it in Column A same line as Column B code used to match. I've never done a "results from a 3rd column" formula so I have no doubt I'm missing something.

Example of Spreadsheet columns

For example: B2 is A01.01 -- it matches E2, so I'd like to have it pull D2 which is 002.0 into A2.

B4 is A35. It matches E6, so I'd like to have it pull D6 which is 037 into B4.

If no match, it can either leave it blank or show #N/A, I don't mind which.

The columns are NOT the same -- I have B1:B3717, while D & E are both 1:3809.

MY problem is I've tried every trick I know with Index Match and I think I'm missing something -- I've tried doing this with the results to an A column as well as the F column (I know some go right to left, some left to right) -- but my biggest problem is (a) getting the system to search the entire list in column E, AND (b) getting the results to pull correctly from Column D to Column A (I keep getting the "last" one until it pulls a new one?

The formula that at least pulls something is =INDEX(D2:D3809,MATCH(B2,E2:E3809)) -- BUT, it does NOT include the 0 for exact match (every time I add the zero to indicate an exact match ALL I get are #N/A. BUT with the formula NOT indicating exact match, I'm getting a few correct ONLY because the line to the right of the B item matches. With the formula listed, I'm getting results lines as follows: 2 - 002.0 correct 3 - 011.9 correct 4 - 012.80 correct 5 - N/A incorrect -- A35 should match E6 and give a result of 037 6 - 037 -- incorrect, no match so no result should be listed this is previous result duplicate 7 - 038.10 which is correct 8 - 038.43 which is correct - same line I seem to be getting #N/A's on some lines that don't match, but per #6 above, that shouldn't have had a result either.

Again, I've gone through multiple answers and tried what I could that seemed to be similar, as well as the Top Mistakes when Using INDEX MATCH.

Any guidance would be appreciated.

Michelle J.

2
excelindexingmatchmultiple-columns
If you are only getting the error when using 0 in the last Criterion, look at your data. There are spaces or other non printable characters that are keeping the match from finding the exact match. - Scott Craner
pnuts greatly appreciate the time & answer. I still had a couple issues but between yours & Alexis I have gotten this to work! thank you again, Michelle - MRJ

2 Answers

1
votes

Replicating your data I can get it to work properly in my worksheet by locking the range references and making it an exact match as follows:

=INDEX($D$2:$D$3809,MATCH(B2,$E$2:$E$3809,0))

or alternatively,

=INDEX(D:D,MATCH(B2,E:E,0))

as @pnuts suggested.

If the exact match isn't returning anything where you think it should, then it's probably not your formula that is an issue, but rather some formatting problem making them not match. Try applying CLEAN and TRIM functions to columns B and E to see if you can get successful matches.

-1
votes

I think you can try with:

=INDEX(D$2:E$3809,MATCH(B2,E$2:E$3809,0),1)