HLookup/Needing Formula Question
 

Sheet 1
A B C D
1 Location Item Qty. Lot#
2 001a Apples 23 5998
3 002a Oranges 41 6335
4 003a Lemons 27 1448
5 004a Grapes 10 2889

Sheet 2
A B C
1 Location Item Qty. Lot#
2 002a Oranges 41 6335



I am trying to figure out a formula that will find data in a row and give back the word, "True" if all data is in that row in Sheet 2 from Sheet1. Can anyone help?

Above is just an example. With this example, let's assume that I am trying to find the info in Sheet 2 row 1.

HLookup/Needing Formula Question
 

hi Munster,

the formula is an array formula to insert with CTRL+Shift+Enter

=IF(ISERROR(MATCH(A2&B2&C2&D2,Sheet1!A:A&Sheet1!B: B&Sheet1!C:C&Sheet1!D:D,0)),FALSE,TRUE)

isabelle

Le 2016-06-20 Ã* 20:09, Munster a écrit :
Sheet 1 A B C D 1 Location
Item Qty. Lot# 2 001a Apples 23 5998 3 002a
Oranges 41 6335 4 003a Lemons 27 1448 5 004a
Grapes 10 2889

Sheet 2 A B C 1 Location Item Qty. Lot# 2
002a Oranges 41 6335



I am trying to figure out a formula that will find data in a row and give
back the word, "True" if all data is in that row in Sheet 2 from Sheet1. Can
anyone help?

Above is just an example. With this example, let's assume that I am trying
to find the info in Sheet 2 row 1.

HLookup/Needing Formula Question
 

or else maybe,
=IF(ISERROR(MATCH(A2&B2&C2&D2,Sheet1!A:A&Sheet1!B: B&Sheet1!C:C&Sheet1!D:D,0)),"",TRUE)

Le 2016-06-20 Ã* 22:48, isabelle a écrit :
hi Munster,

the formula is an array formula to insert with CTRL+Shift+Enter

=IF(ISERROR(MATCH(A2&B2&C2&D2,Sheet1!A:A&Sheet1!B: B&Sheet1!C:C&Sheet1!D:D,0)),FALSE,TRUE)


isabelle

Le 2016-06-20 Ã* 20:09, Munster a écrit :
Sheet 1 A B C D 1 Location
Item Qty. Lot# 2 001a Apples 23 5998 3 002a
Oranges 41 6335 4 003a Lemons 27 1448 5 004a
Grapes 10 2889

Sheet 2 A B C 1 Location Item Qty. Lot# 2
002a Oranges 41 6335



I am trying to figure out a formula that will find data in a row and give
back the word, "True" if all data is in that row in Sheet 2 from Sheet1. Can
anyone help?

Above is just an example. With this example, let's assume that I am trying
to find the info in Sheet 2 row 1.

HLookup/Needing Formula Question
 

Hi,

Am Mon, 20 Jun 2016 17:09:47 -0700 (PDT) schrieb Munster:

Sheet 1
A B C D
1 Location Item Qty. Lot#
2 001a Apples 23 5998
3 002a Oranges 41 6335
4 003a Lemons 27 1448
5 004a Grapes 10 2889

Sheet 2
A B C
1 Location Item Qty. Lot#
2 002a Oranges 41 6335

another suggestion:
=ISNUMBER(MATCH(A2&B2&C2&D2,Sheet1!$A$1:$A$100&She et1!$B$1:$B$100&Sheet1!$C$1:$C$100&Sheet1!$D$1:$D$ 100,0))
and insert the array formula with CTRL+Shift+Enter

In the new Excel versions you can also use (without array):
=COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!C:C,C 2,Sheet1!D:D,D2)0


Regards
Claus B.
--
Windows10
Office 2016