Vlookup worksheet, find value in any column

P

PeterH

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.
 
T

Teethless mama

Assuming your data in A1:G100
H1: holds criteria

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

PeterH

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.
 
T

Teethless mama

how about this:

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


PeterH said:
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 said:
Assuming your data in A1:G100
H1: holds criteria

=IF(COUNTIF(A1:G100,H1),H1,"NOT FOUND")
Click to expand...
Click to expand...
 
T

T. Valko

Countif isn't reliable for use on closed files.

Use Sumproduct instead.

Biff

Teethless mama said:
how about this:

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


PeterH said:
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 said:
Assuming your data in A1:G100
H1: holds criteria

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
P

PeterH

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

Teethless mama said:
how about this:

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


PeterH said:
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 said:
Assuming your data in A1:G100
H1: holds criteria

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Top