Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Needing to know what formula to use | Excel Worksheet Functions | |||
Help, I posted a question on the 18th and am still needing a solut | Excel Programming | |||
needing a formula i think? | New Users to Excel | |||
Needing a formula | Excel Discussion (Misc queries) | |||
NEEDING FORMULA Please | Excel Worksheet Functions |