Index/Match with multiple criteria

**narrowgate88** · 03-27-2013, 04:25 PM

I was given a template to fill in. I have manipulated the data that I had into the same format. Now I just need to take my data and dump it into the template. I'm thinking I need some sort of index/match, but I can't figure it out. I need to populate the Daily column on the RadGridExport tab with the info in the daily column on the reformatted tab. I need to match Facility and Job Name on both to get the daily value. File is attached.

**FDibbins** · 03-27-2013, 04:44 PM

This array formula will give you what you want. Remember to enteer it with CTRL SHIFT enter...

=IFERROR(INDEX(Reformatted!A1:G3000,MATCH(RadGridExport!A2&RadGridExport!C2,Reformatted!A1:A3000&Reformatted!B1:B3000,0),MATCH(RadGridExport!E$1,Reformatted!$A$1:$G$1,0)),"")

An alternative without using an array formula (an array formula over that many rows may slow your system down), wiyjd be to add a helper column on sheet 2 and copy this down (I used H)...
=A2&B2
Then in sheet1 dailty, use this...
=INDEX(Reformatted!C1:C3000,MATCH(RadGridExport!A2&RadGridExport!C2,Reformatted!H1:H3000,0),1)

**singapore** · 03-27-2013, 04:51 PM

Hi,

Alternatively, you can use vlookup function and a helper column together. Formula is in the attached file.

**narrowgate88** · 03-27-2013, 04:50 PM

I like the second option, so I added the helper column. But when I put the formula into the daily column, I got the #N/A error.

**FDibbins** · 03-27-2013, 04:57 PM

@ singapore, that option will only work if the helper is added before the "daily" column (I didnt look at your file, so maybe thats where you put it?)

@ narrowgate, I put that formula in sheet1, E2, and it references sheet2 columns C:H, is that the correct way round? It is pulling data FROM sheet2 INTO sheet1?

**narrowgate88** · 03-27-2013, 04:57 PM

That worked fine. Thank you very much.

**narrowgate88** · 03-27-2013, 04:59 PM

@ singapore - as it turns out some of the data is missing, but that could be a formatting issue.

@fdibbins - yes, it is pulling info from Sheet 2 into Sheet 1

**FDibbins** · 03-27-2013, 05:09 PM

take a look at the attached.

I also noticed that some of your Job names dont match from 1 sheet to the other...
Assistant Administrator (sheet1 C6)
Asst Administrator (sheet2, B3)

For your calcs to be accurate, all names need to be identical. Excle will see Asst Administrator as being different to Assistant Administrator

**narrowgate88** · 03-27-2013, 05:11 PM

That is a good point. So I really should use Job Code instead of Job Description. @singapore's used Job Code. I think the rest of my variances are related to inconsistencies in labeling.