index/match error
 

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=A 35)*(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.

index/match error
 

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.
"Northo111" wrote:

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=A 35)*(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.

index/match error
 

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

"Northo111" wrote:

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=A 35)*(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.