Copying selected data to 2nd workbook based on x criteria

[cdotman]

Hi - I have been trying to figure out a way to copy cells from one worksheet to another worksheet based on a match (hard text entered cell) - but only want certain cells from the matching row. Trying to figure this out all afternoon and evening - and I think I'm running into issues naming the data range or something between cells because the formula builder keeps returning "invalid" when try to copy information from other workbook.

If it matters I'm using Office for Mac 2011.

In attached example I'm trying to populate columns B through F on the "Subdata" worksheet from values in the row on the "Data" worksheet - based on the row as defined by the "Prospect ID."

I was trying to use a nested formula of INDEX and MATCH - but again having problems with invalid data when selecting cells/ranges from the other sheet. For some reason can't even get a single verifiable return value using INDEX - the row_num and column_num arguments don't work for some reason. :/

I have accomplished a similar task - but it was with SUMIFS function and a range of cells - not just a single cell.

There are probably multiple ways to accomplish this task - I'd prefer to use a formula, don't know anything about pivot tables and/or macros.

Would appreciate any help/direction!!

Thanks so much - in advance - to all of the Excel geniuses out there!

[cdotman]

Oops! Thread title is wrong - it's should state 2nd Worksheet (not workbook) - this is all within the same workbook.

[FDibbins]

i cant open your file coz i only have 2003 here at home, but are you using a cell range, or a named range? if you are using a named range, delete it and just use a cell range.

also, try and build the formula on the same sheet as the data 1st, to see if there is a problem reading btw sheets (there shouldnt be tho)
have you tried using vlookup() instead of index/match? (if the data is tabulated to allow that)