Index + Match returning incorrect data

[solleksfan]

Hello all, first post. (yayyy)

Excel 2010

I have kind of a mess on my hands. I am trying to create something a little more visually appealing than a 50 page report of lines and lines of info. My boss wants to be able to see the result of every inspection point and whether or not it is normal or abnormal. What I came up with is attached. I pull the information from our database (we can't create our own reports with this database yet) and put in on sheet two (Import) of the workbook. Then I divided the information into tables by equipment type and wrote some Index/Match lines to bring the info over from sheet two. Then I conditionally formatted the destination cells based on the return. Easy as pie...right?

Lo and behold I come across this beauty. As you can see on sheet 1 (Summary) at the top it says there is 1 abnormal finding, but when I scroll through the file, all green. I found the culprit at the top of the page in D35. On Import, row 625, colum I is "Dirty", but D35 returns "Good" for some reason. I have tried adding more detail to the first MATCH lookup value, and it works, but then the other cells around it won't pull data correctly. Does it have to do with using the wildcard function with the exact match? I would rather not have to go back through every cell and fill out the entire name considering the rest of the name is not useful information.

Thank you in advance for any help you can offer.

Shawn

[Richard Buttrey]

The D34 formula that's showing an error is, unlike the other formulae, a normal formula. The others are all array formulae. i.e.they have been entered with Ctrl-Shift-Enter.

Do the same with D34.

[iceman62k]

Why not use a pivot table like this:
pivottest.PNG

[solleksfan]

The D34 formula that's showing an error is, unlike the other formulae, a normal formula. The others are all array formulae. i.e.they have been entered with Ctrl-Shift-Enter.

Do the same with D34.

Thank you for the reply.

That was my bad on how I saved it before I uploaded it. I still had the same problem when correctly entered as an array.

[solleksfan]

Why not use a pivot table like this:
Attachment 536733

I guess I need to work on my pivot table skills...I could not get it to like this nice. However, this would not tell me what was wrong with MV-TX-1, I fear that adding that information to the pivot table would make it a little too much, but I will try it.

Thank you so much for the reply and help.

[JeteMc]

In addition to Iceman's fine Pivot Table solution you might also want to take a look at this.
The array entered formula* in D7:H39 has been modified so that it reads:

Please Login or Register  to view this content.

Let us know if you have any questions.

[solleksfan]

In addition to Iceman's fine Pivot Table solution you might also want to take a look at this.
The array entered formula* in D7:H39 has been modified so that it reads:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thank you Jete. This seems to do the trick as well. Is this more efficient than the INDEX/MATCH function if I were to type out the complete name without the wildcard? (Assuming that would fix the problem...)

[solleksfan]

Iceman62k, I am trying to replicate your example on the pivot table, and I cant seem to format the highlight the way you did. Is there a trick to formatting only one column when there are multiple columns in one column label selection e.g. Abnormal and Normal?

EDIT*

I got the Abnormal/Normal formatting down, but I can't seem to highlight the text like you did, unless I do it cell by cell.

[JeteMc]

The asterisk is the problem for sure, but perhaps not in the way your thinking. Look at the formula used in the file attached to post #1 with cell D35 selected. Reenter that formula as an array entered formula and run the 'Evaluate Formula' feature, and notice that the row number given is the 609th (actually row 610 since the array being searched begins at row 2). When you look over at the 'Import' sheet H610 displays 'Nitrogen System Gauges' so it appears that the wildcard is getting the formula to find any thing that begins with 'MV-TX-1 [' and ends with 'Gauges' (as it should BTW). So down the road were you to add say 'Hoses' or 'Valves' then that formula would again find those in the 'Nitrogen System' group (I hope this makes sense).
The formula proposed in post#6 will doesn't cause that situation to happen. It can also be put in one cell and copied down and across, as is, opposed to being modified 4 more times, which would happen if the full names had to be typed in. In that sense I would say it is efficient.
Let us know if you have any questions. If on the other hand your question has been answered and issue resolved to your satisfaction, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[solleksfan]

Thank you JeteMC. I appreciate everyone taking the time to help.