error formula locating similar data

[Mage]

hello
i got 4 columns and my formula in A is not working properly

i intend to find similarities for cases where B = C and D=E
then print Yes in A, only if both as above applies and not only one

a good example is A8
a wrong one is A17 which is not working since dates are similar and should print yes !!

i apperciate checking my formula..Thank you

[antoka05]

try with:

=if(match(B2,D:D,0)=match(C2,E:E,0);"yes";"")

Regards,
Antonio

[Mage]

try with:

Regards,
Antonio

Dear Antonio I am not able to make it work when i paste
formula in A2 I get nothing

thanks

[antoka05]

In attached file I've inserted the formula but I I had to use another column, 'G'.

Regards,
Antonio

[phil_b]

might be a better solution due to data type differences but this is without an additional column:

Please Login or Register  to view this content.

[antoka05]

If attached file can help, here I used one column formula.

Regards,
Antonio

[Mage]

If attached file can help, here I used one column formula.

Regards,
Antonio

Dear Antonio
I appreciate your help however i still can not get it to work
example per attached i added 2 rows 18 and 19 but no results
kindly would you suggest some more help
thanks a lot

[antoka05]

You are right, previous formula doesn't work fine.
See attached file, I added this formula in cell 'A2' and then I copied it down:
=IF(SUM(IF(B2&C2=D$2:D$19&E$2:E$19,1,0))>0,"yes","")
but this is array formula and when you type in formula you need to press Ctrl+Shift+Enter and formula will appear with two braces on the sides.

Regards,
Antonio

[daddylonglegs]

You could also try a SUMPRODUCT version which wouldn't need to be "array entered", e.g. in A2 copied down

=IF(SUMPRODUCT((B2=D$2:E$17)*(C2=E$2:E$17)),"yes","")

[Mage]

dear all this was really helpful
however Antonio gave exactly what i wanted, you got my reputation
thanks a lot you saved our office from tons of papers

[Mage]

hello again, dear all

amendment is required to the formula

=IF(SUM(IF(B2&C2=D$2:D$19&E$2:E$19,1,0))>0,"yes","")

as per attached , instead of "yes" in A , i need to copy data from F into A when match happens as in A14 an example

thanks again

[Mage]

any help on this one please ?
thanks

[antoka05]

I applied new formula in attached file but I had to use another column, for edxample'G' column... may be there is a better way to do it.

Regards,
Antonio

[antoka05]

In attached file I used only one column for formula but in this case please put a blank two lines below the last cell in column F, i.e. if the last cell with data is F20 put a blank in cell F22.

Regards,
Antonio

[Mage]

In attached file I used only one column for formula but in this case please put a blank two lines below the last cell in column F, i.e. if the last cell with data is F20 put a blank in cell F22.

Regards,
Antonio

works very well...
much appreciate

[daddylonglegs]

Perhaps a more "standard" approach, which doesn't require and blank cells

=IFERROR(INDEX(F$2:F$19,MATCH(B2&C2,D$2:D$19&E$2:E$19,0)),"")

[Mage]

thanks a lot

[Mage]

dear Antonio and daddylonglegs

formulas worked good and solved big part of the issue
reffering to the the previously attached (test4) i had to use new data source in column d and the formula is not working any more, data is after doing text to column
can you please take a look at attached and see if there is any difference between D and H formats (D is working but not H)!

Thank you