compare two column each other

[majidsiddique]

hi all:

i comparing two table with formula: serial and Lot
i used this formula:

=IF(H2=0,IF(C2=I2,"match","not match"),VLOOKUP(H2,$B$2:$B$21,2,0))
column can be assumed as excel file.

vlookup paste value, i want paste match or not match. kindly tell me is it possible with formula. i am using if when condition false but not working.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 807"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ITEM[/TD]
[TD]SERIAL[/TD]
[TD]LOT[/TD]
[TD]EXPIRY[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]R. #[/TD]
[TD]Serial #[/TD]
[TD]Lot[/TD]
[TD]Exp Date[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]MD-120100000[/TD]
[TD] [/TD]
[TD]2018031054[/TD]
[TD]31/03/2021[/TD]
[TD]120[/TD]
[TD][/TD]
[TD]MD-120100000[/TD]
[TD] [/TD]
[TD]2018031054[/TD]
[TD]31/03/2021[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]2018031305[/TD]
[TD]31/03/2021[/TD]
[TD]200[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018031305[/TD]
[TD]31/03/2021[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]MD-120120000[/TD]
[TD] [/TD]
[TD]2018030052[/TD]
[TD]31/03/2021[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]MD-120120000[/TD]
[TD] [/TD]
[TD]2018030052[/TD]
[TD]31/03/2021[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]MD-120130000[/TD]
[TD] [/TD]
[TD]2018031058[/TD]
[TD]31/03/2021[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]MD-120130000[/TD]
[TD] [/TD]
[TD]2018031058[/TD]
[TD]31/03/2021[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]2018040083[/TD]
[TD]30/04/2021[/TD]
[TD]100[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018040083[/TD]
[TD]30/04/2021[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]2018050903[/TD]
[TD]31/05/2021[/TD]
[TD]160[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2018050903[/TD]
[TD]31/05/2021[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]MD-120160000[/TD]
[TD] [/TD]
[TD]2018041165[/TD]
[TD]30/04/2021[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]MD-120160000[/TD]
[TD] [/TD]
[TD]2018041165[/TD]
[TD]30/04/2021[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]MD-500DM2700[/TD]
[TD]27M882574[/TD]
[TD] [/TD]
[TD]12/03/2023[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]MD-500DM2700[/TD]
[TD]27M882574[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]27M887620[/TD]
[TD] [/TD]
[TD]12/03/2023[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]27M887620[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]27M887911[/TD]
[TD] [/TD]
[TD]14/03/2023[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]27M887911[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]27M887954[/TD]
[TD] [/TD]
[TD]14/03/2023[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]27M887954[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]27M887962[/TD]
[TD] [/TD]
[TD]14/03/2023[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]27M887962[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]27M887979[/TD]
[TD] [/TD]
[TD]14/03/2023[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]27M887979[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]MD-617700000[/TD]
[TD] [/TD]
[TD]1020[/TD]
[TD]28/03/2023[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]MD-617700000[/TD]
[TD] [/TD]
[TD]1020[/TD]
[TD]28/03/2023[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]MD-6207-SI00[/TD]
[TD] [/TD]
[TD]W4252877[/TD]
[TD]18/04/2020[/TD]
[TD]70[/TD]
[TD][/TD]
[TD]MD-6207-SI00[/TD]
[TD] [/TD]
[TD]W4252877[/TD]
[TD]18/04/2020[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]MD-681300000[/TD]
[TD] [/TD]
[TD]2018031008[/TD]
[TD]31/03/2021[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]MD-681300000[/TD]
[TD] [/TD]
[TD]2018031008[/TD]
[TD]31/03/2021[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]MD-6947M-62C[/TD]
[TD]TDK016458G[/TD]
[TD] [/TD]
[TD]07/05/2020[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]MD-6947M-62C[/TD]
[TD]TDK016458G[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]TDK016459G[/TD]
[TD] [/TD]
[TD]07/05/2020[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]TDK016459G[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]TDK016462G[/TD]
[TD] [/TD]
[TD]07/05/2020[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]TDK016462G[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]TDK016463G[/TD]
[TD] [/TD]
[TD]07/05/2020[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]TDK016463G[/TD]
[TD] [/TD]
[TD]01/01/1900[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[majidsiddique]

formula is using column L:

 

[majidsiddique]

please reply anyone.

 

[Fluff]

Rather than showing a formula that doesn't work, try explaining (in words) what you are trying to do.

 

[KolGuyXcel]

The vlookup part in the formula

=IF(H2=0,IF(C2=I2,"match","not match"),VLOOKUP(H2,$B$2:$B$21,2,0))

will not work. Because the table array selected "$B$2:$B$21" is single column table array and we are asking VLOOKUP to return the value from the 2nd column!!

 

[majidsiddique]

i want to show "match", "not match" when condition false,

=IF(H2=0,IF(C2=I2,"match","not match"),VLOOKUP(H2,$B$2:$B$21,2,0))

when H2=0 condition wrong, then match b2=h2, "match", "not match", then condition of "OK", match c2=i2

 

[majidsiddique]

fluff:

i want to show "match", "not match" when condition false,

=IF(H2=0,IF(C2=I2,"match","not match"),VLOOKUP(H2,$B$2:$B$21,2,0))

when H2=0 condition wrong, then match b2=h2, "match", "not match", then condition of "OK", match c2=i2

have you got my point? is there more clarification please?

majid

 

[Fluff]

Still not sure I understand, but try this
=IF(AND(H2=0,C2=I2),"match",IF(H2=B2,"match","no match"))

 

[majidsiddique]

Fluff:

i used this but there is a problem, when serial end at B14 and lot start C15 mention below. it is the break of IF condition with AND.

[TABLE="class: cms_table_grid, width: 807"]
<tbody>[TR]
[TD]MD-617700000[/TD]
[TD][/TD]
[TD]1020[/TD]
[TD]28/03/2023[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]MD-617700000[/TD]
[TD][/TD]
[TD]1020[/TD]
[TD]28/03/2023[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

if i change value LOT 1021 but result "MATCH" because AND condition valid on this, but actually it is false "NOT MATCH". so that is a problem.

 

[majidsiddique]

fluff:

i use in different way, its work properly.

=IF(AND(H2=0,C2=I2),"match",IF(AND(C2=I2,H2=B2),"match","no match"))

Thanks a lot.

majid