Table to Table
 

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?

Table to Table
 

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?

Table to Table
 

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?

Table to Table
 

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?