Pulling data from one sheet to another using index match

[lesiahawk]

Hey all!

I am trying to pull data from one sheet and insert it into another and I believe Index Match is the formula I need to use, but it's not one that I'm familiar with enough to put together. I have attached a copy of the spreadsheet. The 3 yellow columns (B, D, O) on the "AgentSession.RPT" sheet contain the data that I need to pull and insert on the "Data" sheet in columns A, B and C.

However, all I need from the "AgentSession.RPT" sheet in Column B are the agent names. Column D, I need exactly what's listed. Column O, I only need the top number for each agent (See Cell O11, O15, O19 and O23).

Once this information has been extracted properly, I need it to look like the "Data" sheet.

Thank you in advance for any assistants that you can give. This is a multi-part project, but I'm lost on part 1.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[lesiahawk]

Thank you AliGW! My apologies. I have corrected the title.

[azumi]

Maybe put this on B3 and copied down and cross, and on B1 change to "Session":

=INDEX(AgentSession.RPT!$D$11:$Q$24,MATCH(INDEX(AgentSession.RPT!$B$11:$B$24,MATCH($A3,AgentSession.RPT!$B$11:$B$24,0)-1),AgentSession.RPT!$B$11:$B$24,0),MATCH(B$1,AgentSession.RPT!$D$8:$Q$8,0))

[lesiahawk]

Thanks Azumi! That works perfectly. But I'm curious, why does it not work for column A on the Data sheet?

[azumi]

Have you changed cell B1 to "Session" (without double quotes)

[lesiahawk]

Yes, and it works for the cells in column b and c. However, if I try to use the same formula in column a to pull the agent, names from the AgentSessionRPT Sheet, I get errors in column a.

[azumi]

I don't understand for column A which one? My formula is using criteria/condition on cell A3 to AA6 and for column header on cell B1 and C1, you can change column header on B1 and C1 as exactly on your data sheet like "Sysrtem" or "Wrap" or "Idle", etc

[lesiahawk]

My apologies. I am trying to pull the names from Column B on the "AgentSession.RPT" sheet and insert them into Column A on the "Data" sheet also. I don't want to have to manually enter these names every week because eventually the "AgentSession.RPT" and the "Data" sheets will be in 2 separate work books that I will have to reference in order to create the same result.

[lesiahawk]

Azumi did you have a chance to take another look at the formula?

[JeteMc]

Try:

Please Login or Register  to view this content.

Note that if the "AgentSession.RPT" sheet will be in a different workbook, then my understanding is that these formulas will only work while both workbooks are open.
Let us know if you have any questions.

[lesiahawk]

Thanks JeteMC! I'm going to need a little help with this one. I tried plugging the formula you suggested on the Data Sheet in Cell A3 (left cell B1 as "System") but it returned a blank cell. I did the same for cells A3:C6 and all cells returned blank. I wasn't able to pick the formula apart enough to determine where the blank was coming from.

[JeteMc]

Sorry about that, I overlooked that you are using the 2007 version which does not support AGGREGATE. I replaced that formula with the following array entered formula*:

Please Login or Register  to view this content.

*Note that to activate this formula you will need to:
1. Select cell A3,
2. Paste the formula into the formula bar,
3. Simultaneously press the Ctrl, Shift and Enter keys.
You may then copy down (this formula is only for column A).
Here is a copy of the original file with the formula I posted applied to column A and Azumi's formula applied to columns B:C
Let us know if you have any questions.

[lesiahawk]

AWESOME! Everything works PERFECTLY!! Thanks JeteMC!

[lesiahawk]

I might have spoken to soon. When I separated the worksheets into 2 separate workbooks (Agent Session Report & Data) and overrode the data currently in the Agent Session Report workbook with new data, Column A on the Data workbook pre-fed the names with no problem from Column B on the Agent Session Report workbook. However, Column B and C on the Data workbook return #N/A. I'm not sure why.

[lesiahawk]

I figured it out!!! Thanks everyone!!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have blessed day.