The following is working that if the same item exist in G7 and in
item.xls(item!)
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

But I have to check two sheet Item! and Stock! in item.xls. If the G7
value may exist either in Item1 sheet or in stock! sheet. So please help me
again to incorparate this changes with the existing formula
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

=IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0).

You could string it to check in sequence like this, indicatively:
=IF(COUNTIF(1),VLOOKUP(1),IF(COUNTIF(2),VLOOKUP(2) ,0),0)

A subtle point is the significance of the sequence. Since the IF evaluates
from left to right, if (1) is TRUE, (2) will never get evaluated. You should
place the more "reliable" reference sheet to be (1) within the expression, eg
if the reference sheet: item is better, place it in (1) to be checked ahead
of "stock"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"pol" wrote:
The following is working that if the same item exist in G7 and in
item.xls(item!)
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

But I have to check two sheet Item! and Stock! in item.xls. If the G7
value may exist either in Item1 sheet or in stock! sheet. So please help me
again to incorparate this changes with the existing formula
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

=IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0).

What happens if the item exists in both tables?

Maybe you want something like:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0))

Regards,
Fred

"pol" wrote in message
...
The following is working that if the same item exist in G7 and in
item.xls(item!)
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

But I have to check two sheet Item! and Stock! in item.xls. If the G7
value may exist either in Item1 sheet or in stock! sheet. So please help
me
again to incorparate this changes with the existing formula
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

=IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0).

Greate! Thanks a lot

"Fred Smith" wrote:

What happens if the item exists in both tables?

Maybe you want something like:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0))

Regards,
Fred

"pol" wrote in message
...
The following is working that if the same item exist in G7 and in
item.xls(item!)
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

But I have to check two sheet Item! and Stock! in item.xls. If the G7
value may exist either in Item1 sheet or in stock! sheet. So please help
me
again to incorparate this changes with the existing formula
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

=IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0).

You're welcome. Thanks for the feedback.

Regards,
Fred

"pol" wrote in message
...
Greate! Thanks a lot

"Fred Smith" wrote:

What happens if the item exists in both tables?

Maybe you want something like:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0))

Regards,
Fred

"pol" wrote in message
...
The following is working that if the same item exist in G7 and in
item.xls(item!)
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

But I have to check two sheet Item! and Stock! in item.xls. If the
G7
value may exist either in Item1 sheet or in stock! sheet. So please
help
me
again to incorparate this changes with the existing formula
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

=IF(COUNTIF([item.xls]stock!B:B,G7),VLOOKUP(G7,[item.xls]stock!B:L,11,0),0).