Index/Match Function Three Fields

**BrandyBarrs** · 11-16-2014, 08:58 PM

Hi,

I have a spreadsheet that I would like the report template to have an "index/match" formula to pull data based on three fields. I would like the "report template" sheet to pull the value in a matching cell on the "operations" tab based on the vendor name located in cell "A4-I5", then the matching question in cell "A10-A11" and then the matching survey grade located in cell "B9" and return that value to cell "B10".

For instance, if the vendor name in cell "A4-I5" was 9to5 Seating then the spreadsheet would look for the response to the question in cell "A10-A11" would return the number "0" for the failed consistently and "1" for the needs improvement field and so forth. I've attached a copy of the workbook for review which has the formula I've been trying to work on to solve the issue.

Your help would be greatly appreciated!!

**FDibbins** · 11-16-2014, 09:27 PM

Hi, welcome to the forum

1st, make sure your headings match in each table, it makles things SO much easier, eg Ops sheet ="Failed-Consistently Lacking", but Report sheet ="Failed Consistently"

Then use this, copied down and across...
=INDEX(Operations!$C$2:$P$109,MATCH('Report Template'!$A$4&'Report Template'!$A10,INDEX(Operations!$A$2:$A$109&Operations!$B$2:$B$109,0),0),MATCH('Report Template'!B$9,Operations!$C$1:$P$1,0))

On a side note, I would try and avoid all those merged cells if you can, they cause nothing but problems with formulas - and there are other ways to get the same look. For instance, you can widen the rows and highlight the entire "row" range, go into Format/Alignment and select Center Across Selection

**BrandyBarrs** · 11-16-2014, 11:50 PM

I appreciate the feedback. Normally, I wouldn't merge cells in this manner but this is what my boss specifically wanted and I pick and choose my battles wisely and since this is only an annual report, I decided against the argument this time around.

Other than that, this formula worked beautifully. It works just as expected. Thanks so much for your help.