Match two columns across sheets and based on criteria of another column

[tarakul]

Hi All

This is driving me nuts. What I have is:

Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text

Sheet 2

ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values

What I want is in Column F (Sheet1) a formula to:

if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True

This must be possible - Please Help

Thanks

 

[sanrv1f]

try this

=IF(ISNUMBER(MATCH(B2,Sheet2!A:A,0)),VLOOKUP(B2,Sheet2!A:AX,50,0)>1)

 

[Aladin Akyurek]

Hi All

This is driving me nuts. What I have is:

Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text

Sheet 2

ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values

What I want is in Column F (Sheet1) a formula to:

if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True

This must be possible - Please Help

Thanks

Sheet1

A2:

Control+shift+enter, not just enter...

=ISNUMBER(MATCH(1, 
    IF(Sheet2!$A$2:$A$400=B2, 
    IF(ISNUMBER(Sheet2!$AX2:$AX$400), 
    IF(Sheet2!$AX2:$AX$400 > 1, 1))),0))

Note. If AX is numeric, Sankar's suggestion would more appropriate.

 

[tarakul]

First formula - Brilliant! That worked. Could you do me two huge favours??? (Thanks for your response Aladin too but the first formula worked!)

1. explain what the first formula is doing
2. is it possible to amend the formula so as well as looking to see if AX>1 it checks AB<>"Text1" AND AC<>"Text2"

Thanks!

 

[sanrv1f]

the first part

ISNUMBER(MATCH(B2,Sheet2!A:A,0)

checks, availablity of the number in column A,

if available, the next part

VLOOKUP(B2,Sheet2!A:AX,50,0)

takes the 50th column (AX) in the corresponding row and checks, if >1

both the steps are true, the output is a TRUE, if any of the 2 steps fails, result is FALSE

here is a suggestion for the next question

=SUMPRODUCT(--(Sheet2!A1:A100=B2),--(Sheet2!AX1:AX100>1),--(Sheet2!AB1:AB100="Text1"),--(Sheet2!AC1:AC100="Text2"))>0

note: you can't use whole column reference in SUMPRODUCT (as against VLOOKUP / MATCH)

 

[tarakul]

Wow thanks!

Now I want to use the true or false in combination with a sumproduct formula in another sheet to sum using multiple criteria, one of which will be when this column equals true, but it doesnt seem to pick it up:

=SUMPRODUCT(--('Forecast Data Dump'!$T$2:$T$2001="TRUE"),.........

Doesnt seem to work...is it because the FALSE and TRUE in the column are not strings?

 

[Aladin Akyurek]

First formula - Brilliant! That worked. Could you do me two huge favours??? (Thanks for your response Aladin too but the first formula worked!)

1. explain what the first formula is doing
2. is it possible to amend the formula so as well as looking to see if AX>1 it checks AB<>"Text1" AND AC<>"Text2"

Thanks!

Regarding (2), things are now different...

Control+shift+enter, not just enter...

=ISNUMBER(MATCH(1, 
    IF(Sheet2!$A$2:$A$400=B2,
    IF(($AB$2:$AB$400<>"Text1")*($AC$2:$AC$400<>"Text2"),
    IF(ISNUMBER(Sheet2!$AX2:$AX$400)*
      (Sheet2!$AX2:$AX$400 > 1), 1))),0))

 

[tarakul]

is there an advantage over the second method than the first?

 

[sanrv1f]

Wow thanks!

Now I want to use the true or false in combination with a sumproduct formula in another sheet to sum using multiple criteria, one of which will be when this column equals true, but it doesnt seem to pick it up:

=SUMPRODUCT(--('Forecast Data Dump'!$T$2:$T$2001="TRUE"),.........

Doesnt seem to work...is it because the FALSE and TRUE in the column are not strings?

TRUE/FALSE are booleans, not strings, hence doesn't require the ",

BTW, you dont have to include them in the formula explicitly,

something like below would do the job

=SUMPRODUCT(--('Forecast Data Dump'!$T$2:$T$2001),.........

 

[tarakul]

thank you so much - i should have come to the forum before i wasted a whole day trying to work this out

Thanks!!