Vlookup worksheet, find value in any column
 

I am trying to look a worksheet with several colums, & trying to bring in a
value onto another worksheet if it is in any column. IE don't have to
nominate the column the result should be in.

Vlookup worksheet, find value in any column
 

Assuming your data in A1:G100
H1: holds criteria

=IF(COUNTIF(A1:G100,H1),H1,"NOT FOUND")


"PeterH" wrote:

I am trying to look a worksheet with several colums, & trying to bring in a
value onto another worksheet if it is in any column. IE don't have to
nominate the column the result should be in.

Vlookup worksheet, find value in any column
 

Thanks, I tried - if(countif('[Merged Supplier and
Products.xls]Sheet1'!$W$2:$AF$1012),BS3,"not found") not sure if you got what
I ment, I am looking for a name in a cell on 1 worksheet, to see if it is in
any column on another worksheet.

"Teethless mama" wrote:

Assuming your data in A1:G100
H1: holds criteria

=IF(COUNTIF(A1:G100,H1),H1,"NOT FOUND")


"PeterH" wrote:

I am trying to look a worksheet with several colums, & trying to bring in a
value onto another worksheet if it is in any column. IE don't have to
nominate the column the result should be in.

Vlookup worksheet, find value in any column
 

how about this:

=if(countif('[Merged Supplier and
Products.xls]Sheet1'!$W$2:$AF$1012,BS3),BS3,"not found")


"PeterH" wrote:

Thanks, I tried - if(countif('[Merged Supplier and
Products.xls]Sheet1'!$W$2:$AF$1012),BS3,"not found") not sure if you got what
I ment, I am looking for a name in a cell on 1 worksheet, to see if it is in
any column on another worksheet.

"Teethless mama" wrote:

Assuming your data in A1:G100
H1: holds criteria

=IF(COUNTIF(A1:G100,H1),H1,"NOT FOUND")


"PeterH" wrote:

I am trying to look a worksheet with several colums, & trying to bring in a
value onto another worksheet if it is in any column. IE don't have to
nominate the column the result should be in.

Vlookup worksheet, find value in any column
 

Countif isn't reliable for use on closed files.

Use Sumproduct instead.

Biff

"Teethless mama" wrote in message
...
how about this:

=if(countif('[Merged Supplier and
Products.xls]Sheet1'!$W$2:$AF$1012,BS3),BS3,"not found")


"PeterH" wrote:

Thanks, I tried - if(countif('[Merged Supplier and
Products.xls]Sheet1'!$W$2:$AF$1012),BS3,"not found") not sure if you got
what
I ment, I am looking for a name in a cell on 1 worksheet, to see if it is
in
any column on another worksheet.

"Teethless mama" wrote:

Assuming your data in A1:G100
H1: holds criteria

=IF(COUNTIF(A1:G100,H1),H1,"NOT FOUND")


"PeterH" wrote:

I am trying to look a worksheet with several colums, & trying to
bring in a
value onto another worksheet if it is in any column. IE don't have to
nominate the column the result should be in.

Vlookup worksheet, find value in any column
 

Thanks, you a bloody marvel! Works like a charm.

"Teethless mama" wrote:

how about this:

=if(countif('[Merged Supplier and
Products.xls]Sheet1'!$W$2:$AF$1012,BS3),BS3,"not found")


"PeterH" wrote:

Thanks, I tried - if(countif('[Merged Supplier and
Products.xls]Sheet1'!$W$2:$AF$1012),BS3,"not found") not sure if you got what
I ment, I am looking for a name in a cell on 1 worksheet, to see if it is in
any column on another worksheet.

"Teethless mama" wrote:

Assuming your data in A1:G100
H1: holds criteria

=IF(COUNTIF(A1:G100,H1),H1,"NOT FOUND")


"PeterH" wrote:

I am trying to look a worksheet with several colums, & trying to bring in a
value onto another worksheet if it is in any column. IE don't have to
nominate the column the result should be in.