I have tried to make a formula to extract data from a table (not pivot) into
a grid when 2 variables are selected, but when I select the criteria I get
nothing apart from 0.

=IF(AND(D3<"",B12<""),OFFSET(Table!A1,MATCH(D3,T able!A1:A2206,0),MATCH(B12,Table!B1:DA1,0))," ")

so that it will except any entry in D3 and B12 as long as they Match the
table, but still I am getting no data pulled from the main table, does anyone
see the flaw in my formula?

Your formula works for me, though it needs to have MATCH(D3,Table!A1:A2206,0) changed to
MATCH(D3,Table!A2:A2206,0) - because of your choice to use OFFSET rather than INDEX. Also, if you
are going to try to pull more than one data point from the table, I would change the formula to

=IF(AND(D$3<"",$B12<""),OFFSET(Table!$A$1,MATCH( D$3,Table!$A$2:$A$2206,0),MATCH($B12,Table!$B$1:$D A$1,0)),"
")

HTH,
Bernie
MS Excel MVP


"GBH99" wrote in message
...
I have tried to make a formula to extract data from a table (not pivot) into
a grid when 2 variables are selected, but when I select the criteria I get
nothing apart from 0.

=IF(AND(D3<"",B12<""),OFFSET(Table!A1,MATCH(D3,T able!A1:A2206,0),MATCH(B12,Table!B1:DA1,0))," ")

so that it will except any entry in D3 and B12 as long as they Match the
table, but still I am getting no data pulled from the main table, does anyone
see the flaw in my formula?

Bernie, This is great thanks. Is there anyway to display nothing rather than
a zero though?

"Bernie Deitrick" wrote:

Your formula works for me, though it needs to have MATCH(D3,Table!A1:A2206,0) changed to
MATCH(D3,Table!A2:A2206,0) - because of your choice to use OFFSET rather than INDEX. Also, if you
are going to try to pull more than one data point from the table, I would change the formula to

=IF(AND(D$3<"",$B12<""),OFFSET(Table!$A$1,MATCH( D$3,Table!$A$2:$A$2206,0),MATCH($B12,Table!$B$1:$D A$1,0)),"
")

HTH,
Bernie
MS Excel MVP


"GBH99" wrote in message
...
I have tried to make a formula to extract data from a table (not pivot) into
a grid when 2 variables are selected, but when I select the criteria I get
nothing apart from 0.

=IF(AND(D3<"",B12<""),OFFSET(Table!A1,MATCH(D3,T able!A1:A2206,0),MATCH(B12,Table!B1:DA1,0))," ")

so that it will except any entry in D3 and B12 as long as they Match the
table, but still I am getting no data pulled from the main table, does anyone
see the flaw in my formula?

GBH99,

=IF(AND(D$3<"",$B12<"",OFFSET(Table!$A$1,MATCH(D $3,Table!$A$2:$A$2206,0),MATCH($B12,Table!$B$1:$DA $1,0))<""),OFFSET(Table!$A$1,MATCH(D$3,Table!$A$2 :$A$2206,0),MATCH($B12,Table!$B$1:$DA$1,0)),"
")

Note that this doesn't include any error checking....

HTH,
Bernie
MS Excel MVP


"GBH99" wrote in message
...
Bernie, This is great thanks. Is there anyway to display nothing rather than
a zero though?

"Bernie Deitrick" wrote:

Your formula works for me, though it needs to have MATCH(D3,Table!A1:A2206,0) changed to
MATCH(D3,Table!A2:A2206,0) - because of your choice to use OFFSET rather than INDEX. Also, if
you
are going to try to pull more than one data point from the table, I would change the formula to

=IF(AND(D$3<"",$B12<""),OFFSET(Table!$A$1,MATCH( D$3,Table!$A$2:$A$2206,0),MATCH($B12,Table!$B$1:$D A$1,0)),"
")

HTH,
Bernie
MS Excel MVP


"GBH99" wrote in message
...
I have tried to make a formula to extract data from a table (not pivot) into
a grid when 2 variables are selected, but when I select the criteria I get
nothing apart from 0.

=IF(AND(D3<"",B12<""),OFFSET(Table!A1,MATCH(D3,T able!A1:A2206,0),MATCH(B12,Table!B1:DA1,0)),"
")

so that it will except any entry in D3 and B12 as long as they Match the
table, but still I am getting no data pulled from the main table, does anyone
see the flaw in my formula?