index/match error

N

Northo111

I have the following data in a sheet called growers

Col A Col B Col C
Grower Name Product Size Price

There are 44 rows of data in total

In a second sheet I have setup drop down lists to select the Grower Name in
Col A and Product Size in Col B.

I have entered the following function into another column in cell H2
{=INDEX(growers!C2:C43,MATCH(1,(growers!A2:A43=A2)*(growers!B2:B43=B2),0))}
which matches the price accordingly.
I've then copied the function down column H and everything seemed to be
working fine.
My issue is that when I get to cell H35 (and onwards) I get the #N/A from
that row onwards. Here is the function as it is in cell H35
{=INDEX(growers!C33:C76,MATCH(1,(growers!A33:A76=A35)*(growers!B33:B76=B35),0))}
I have a feeling it has something to do with the lookup array value, however
I can't work out how to fix it.
 
G

Gav123

Hi,

Maybe making your ranges absolute references, then copy down as far you need
might help...

{=INDEX(growers!$C$2:$C$43,MATCH(1,(growers!$A$2:$A$43=A2)*(growers!$B$2:$B$43=B2),0))}

Hope this helps,

Gav.
 
N

Northo111

Thanks Gav. I had tried that previously and couldn't get it to work, however
realised that I wasn't using CTRL+SHIFT+ENTER at the end of entering the
formula

Cheers
Northo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Index Match & #values 0
Index and Match 8
MYSTERY: NESTED INDEX AND MATCH FUNCTIONS 29
Index and Match 3
Index Match Max? 2
VLOOK UP 1
Index Match, IF or Vlookup 0
Index, Match help 6

Top