comparing two columns against two other columns
Posted by yaneckc on July 22, 2000 6:58 PM
I'M TRYING TO COMPARE TWO COLUMNS AGAINST TWO OTHER
COLUMNS.
SECURITY QUANTITY MATCH SECURITY QUANTITY
123456 200 133333 200
234567 180 100 789624 50
345678 500 ALL 234567 100
799611 170 179264 500
133333 200 ALL 345678 500
I NEED TO CREATE FORMULA FOR MATCH .IF SECUIRTY(COL1)
AND QUANTITY(COL2) BOTH MATCH COL4 AND COL5 THEN ENTER
ALL IN MATCH COLUMN. IF ONLY COL1 MATCHES COL4 THEN
ENTER QUANTITY FROM COLUMN 5.
Posted by Ada on July 26, 0100 3:02 AM
YANECKC
If the previous formula does not do what you want, here is the alternative formula :-
=IF(ISNA(VLOOKUP(A1,$D$1:$D10000,1,FALSE)),"", IF(B1=VLOOKUP(A1,$D$1:$E10000,2,FALSE), "ALL", VLOOKUP(A1,$D$1:$E$10000,2,FALSE)))
To see the difference in the results of each formula, change the quantity in the last cell of column C from 500 to 400.
Ada
Posted by Ada on July 23, 0100 3:05 AM
Yaneckc
Put the following formula in C1 and drag it down :-
=IF(A1=D1,IF(B1=E1,"ALL",E1),"")
Ada
Posted by YANECKC on July 27, 0100 5:11 AM
ADA
THANK YOU . THE LAST FORMULA WAS THE ONE
I WAS LOOKING FOR. YOU MADE MY DAY. THANKS AGAIN.
YANECKC
Posted by Ada on July 25, 0100 9:33 AM
YANECKC
Put the following formula in C1 and drag it down :-
=IF(ISNA(VLOOKUP(A1,$D$1:$D10000,1,FALSE)),"", IF(ISNA(VLOOKUP(B1,$E$1:$E$10000,1,FALSE)), VLOOKUP(A1,$D$1:$E$10000,2,FALSE),"ALL"))
There are two ways of interpreting what you want from the sample data you gave, so let me know if the formula does not provide what you want.
Ada
Posted by YANECKC on July 23, 0100 2:38 PM
ADA
ONE PROBLEM. WHEN COMPARING COLUMNS THE MATCHES ARE FOUND ON
DIFFERENT ROWS. BUT ONCE IT MATCHES THE SECURITY(COL1 AND COL4)IT
TRIES TO COMPARE THE CELL DIRECTLY TO THE RIGHT IN COL5
SEE ABOVE EXAMPLE
THANK YOU FOR YOUR HELP
YANECKC
