Matching names to values using SUMPRODUCT

Hi All

First problem was fixed by Divi123 which was bringing values accross from one sheet to another matching certain values using the SumProduct - thanks Divi123. For the last 2 days I've been trying to fix the latest problem but my grasp of Excel is not great.

I have a sheet called "Tracker - Names" (see attached spreadsheet) in the section "Pupils names" I need to be able to bring accross from the "Data" sheet not the corresponding educational levels to 3B (in this instance 4, 3, 1, 2, 4) but the names matching these levels.

For example there are 4 people that match "3B" with a level of "2C" I need their names (LEIGHTLEY Megan, MILO Renat, PARK Chloe and PARK Chloe) to appear in a cell.

Is this even possible?

Thanks in advance

Kevin

Hi Pepe Le Mokko

Thanks for looking at this for me

the 4 pupils have a level 3B (B27 on sheet) but also match a level 2C (D3)

I need to bring back the names that match both the 3B on the horizontal and the 2C on the vertical, which if you look at the data sheet are (LEIGHTLEY Megan, MILO Renat, PARK Chloe and PARK Chloe)

I know it is confusing, it has taken days just to try to work it out in my brain

Thanks again

Kevin

Pl see attached file.

Hi kvsrinivasamurthy

Many thanks for taking the time to look at this problem

Unfortunately there is an #Name? error appearing on all cells - See image attached

Also just out of interest is this the most complicated formula ever

Many thanks for you help

P.S. I am using Excel 2003

Sorry for being a pain

Tried the IF(ISERROR... but got a value error - wanted a value but I just don't understand it, Sorry

Could you add it to the spreadsheet to show me what you mean? I have never come accross a formula like this one.

Pepe Le Mokko this is exactly what I wanted

I have to say I never thought anyone could create formula as complicated as this and make it work.

Please tell me, is it the longest formula you have ever created?

Thanks so much for this

Many, many thanks

Kevin