Index/Match or Sumproduct formula

**pauldaddyadams** · 01-19-2012, 08:30 AM

Hi guys

I am struggling with an index and match formula if anyone knows any good references of where I can learn it please advise, every time I search I find the tutorials too complicated and it would save me having to ask each time.

This is what I need help with please....

I require an index and match or sumproduct formula which does the following:
I need the formula to match B10 on the “UserSheet” with Row 3 on the “Act units” sheet and then I need it to match =B35&C35&D35&E35&F35 on the “UserSheet” with that of column G on the “Act Units” sheet

I have uploaded an example file, I need a formula in the green highlight column.

Can anyone help please?

**Bob Phillips** · 01-19-2012, 08:38 AM

Try

A34: =MATCH($B$10,'Act Units'!$3:$3,0)
G34: =INDEX('Act Units'!A:CT,MATCH($B35&$C35&$D35&$E35&$F$35,'Act Units'!$G:$G,0),$A$34)

Copy G34 down

**pauldaddyadams** · 01-19-2012, 11:21 AM

Thanks Bob that worked!!

**Bob Phillips** · 01-19-2012, 11:26 AM

Exactly the same principle as the one I gave you the other day which you say returned #N/A, so I am surprised.

**pauldaddyadams** · 01-19-2012, 11:30 AM

Hi

I found out why it returned #N/A!...

The list I had contained an extra character e.g. April was displayed as "April " rather than "April" as on the lookup sheet. Once i deleted the space it worked