Index Match formula searching for three criteria

**davechamp1983** · 12-17-2012, 02:14 PM

Good Evening

I have a workbook that I am using with a Vlookup formula but I think an Index/match formula may suit my need better for what I am trying to achieve.

I will attach the work book for people to see but let me explain what I need to do.

I have a report that drops daily information to a centralised spread sheet, What I want my spread sheet to do is look up the three criteria I want and then return the result.

For Example

The three criteria I want to search for in this example are as follows I want to be able to search for Chris Ollerhead in cell A30 then search for Number of calls in Cell A31 and finally search for the date I want the result for in B1.

My index data is in a seperate file called Agent Master Data and the sheet is called Raw Data the Index data Cells are A3:IZ200 (the data isnt all in there yet but I wanted to make enough of an allowance)

The dates are entered in row 1 of this spreadsheet.

I have included both the files I am using to try and make it easier.

I need to do the same procedure but by matching

Time Logged In - Agent Name - Date
Time Logged out - Agent Name - Date
Not Ready - Agent Name - Date
Number of Calls - Agent Name - Date

The AM/Pm break and lunch are entered manually.

Any help would be very much appreciated as I think the Vlookup may not be the right way to go with this.

**dogberry** · 12-17-2012, 02:26 PM

http://support.microsoft.com/kb/59482

Hi

Have a look at the above link it may help you as for Vlookup it may be possible but I think VBA would be needed.

Chris

**FDibbins** · 12-17-2012, 03:00 PM

try this...
in the Agent Master file, raw data sheet, add a helper row after AGENT, then use this...
=B1&B2
that combines the data and the "code"

then in Amy's sheet, C31, use this, copied down and across

=IFERROR(INDEX('[Agent Master Data.xlsm]Raw Data'!$A$3:$GD$159,MATCH($A30,'[Agent Master Data.xlsm]Raw Data'!$A$3:$A$159,0),MATCH(C$1&$A31,'[Agent Master Data.xlsm]Raw Data'!$A$3:$GD$3,0)),"")

you will have to change bolded part $A30 back to $A30 after you copy it, but just for the rest of that person. after that, you can copy the entire range C31:C37, down and across.

I noticed you dont have data in all the fields, nor do you have all the manes. let me know how you make out please?

**davechamp1983** · 12-17-2012, 03:58 PM

I will give this a try, I am only using some test data just to get everything in place and how i want it at the moment, The only problem i see is that my raw data gets dropped in daily in a set format so I wont be able to amend the set up in there but I suppose I could add another tab and pull the fields i want and this sheet could be amended as much as i want.

**FDibbins** · 12-17-2012, 04:11 PM

ok well if you could add a new row at the top, or put the download 1 row down, that concatenation (1st step) could be put at the top, and the 2nd formula adjusted accordingly

**davechamp1983** · 12-17-2012, 05:56 PM

I seem to be struggling with this one.

I have an old formula that I have tried to adapt but I cant get it to work at all heres what I have.

Formula:

Please Login or Register  to view this content.

I have re attached the workbook with my formula (even though its not working LOL)

Thanks again for all your help it is much appreciated.

Index Match formula searching for three criteria

LinkBack

Thread Tools

Rate This Thread

Display

Index Match formula searching for three criteria

Re: Index Match formula searching for three criteria

Re: Index Match formula searching for three criteria

Re: Index Match formula searching for three criteria

Re: Index Match formula searching for three criteria

Re: Index Match formula searching for three criteria

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions