Lookup if two conditions are met!

[MASOODAHMAD]

Hello,

I'm trying to get this to work. I tried the vlookup, combination of Match & Index, but none helped me to achieve the desired output. I'm missing something.

Here is my lookup table1 with "Holiday" details in the third column. Instead of "Holiday", the column might contains other text/value.

Table1:
[TABLE="width: 251"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Ecode[/TD]
[TD="class: xl65, width: 161"]Date[/TD]
[TD="class: xl65, width: 87"]Day[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1247
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday
[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1141[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday
[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-0366[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday
[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1317[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { border: 0.5pt solid windowtext; }.xl65 { font-weight: 700; }.xl66 { text-align: left; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl67 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }</style>

Below is the table2 where I want to extract the values from the table1 above.

If values in column A and B (table2) matches with the values in column A and B (table1), then the corresponding column C (table2) should get the value from column C of table 1. Also note that Ecode is repeated in column A.

For example, in the table below, the Red highlighted needs to have the "Holiday" data in column C.

Table2:
[TABLE="width: 605"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Ecode[/TD]
[TD="class: xl65, width: 161"]Date[/TD]
[TD="class: xl65, width: 87"]Day[/TD]
[TD="width: 25"][/TD]
[TD="width: 87"][/TD]
[TD="width: 25"][/TD]
[TD="class: xl65, width: 87"]Ecode[/TD]
[TD="class: xl65, width: 161"]Date[/TD]
[TD="class: xl65, width: 87"]Day[/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0123[/TD]
[TD="class: xl64, align: right"]2/6/2018, Saturday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0123[/TD]
[TD="class: xl64, align: right"]2/6/2018, Saturday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1246[/TD]
[TD="class: xl64, align: right"]13/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1246[/TD]
[TD="class: xl64, align: right"]13/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0482[/TD]
[TD="class: xl64, align: right"]21/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0482[/TD]
[TD="class: xl64, align: right"]21/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0229[/TD]
[TD="class: xl64, align: right"]29/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0229[/TD]
[TD="class: xl64, align: right"]29/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0362[/TD]
[TD="class: xl64, align: right"]15/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0362[/TD]
[TD="class: xl64, align: right"]15/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1317
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-1317[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1062[/TD]
[TD="class: xl64, align: right"]23/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1062[/TD]
[TD="class: xl64, align: right"]23/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1188[/TD]
[TD="class: xl64, align: right"]25/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1188[/TD]
[TD="class: xl64, align: right"]25/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0683[/TD]
[TD="class: xl64, align: right"]31/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD]OUTPUT >>[/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0683[/TD]
[TD="class: xl64, align: right"]31/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1306[/TD]
[TD="class: xl64, align: right"]6/6/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1306[/TD]
[TD="class: xl64, align: right"]6/6/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1141
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-1141[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0331[/TD]
[TD="class: xl64, align: right"]9/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0331[/TD]
[TD="class: xl64, align: right"]9/5/2018, Wednesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1358[/TD]
[TD="class: xl64, align: right"]11/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1358[/TD]
[TD="class: xl64, align: right"]11/5/2018, Friday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1235[/TD]
[TD="class: xl64, align: right"]4/6/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1235[/TD]
[TD="class: xl64, align: right"]4/6/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]3/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]3/5/2018, Thursday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-0366
[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-0366[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]EKCS-1247[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl66"]EKCS-1247[/TD]
[TD="class: xl67, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKCS-0949[/TD]
[TD="class: xl64, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0949[/TD]
[TD="class: xl64, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl68"]EKCS-0911[/TD]
[TD="class: xl64, align: right"]27/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl68"]EKCS-0911[/TD]
[TD="class: xl64, align: right"]27/5/2018, Sunday[/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { border: 0.5pt solid windowtext; }.xl65 { font-weight: 700; }.xl66 { text-align: left; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl67 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl68 { border: 0.5pt solid windowtext; }</style>

Thanks in advance.

 

[Marcelo Branco]

James

I concatenated "" with the result of the formula to avoid a zero as result when the found cell in column C is empty;

M.

 

[JamesonMH]

James

I concatenated "" with the result of the formula to avoid a zero as result when the found cell in column C is empty;

M.

Ah ok, got it. Thanks for your reply Marcelo.

 

[MASOODAHMAD]

To my understanding, the ""& delivers the blank in case there's no value in the lookup column C. Without ""&, it will deliver '0' zero if both the conditions are met and the lookup column is blank.

Hope I'm right.

 

[DigitalData]

Well if you work around data a lot etc you will learn workarounds and I think this is one of them. The idea here is to get a unique value that you can lookup easily right? Try this.

1. Create a new column and use this formula =CONCATENATE(A2," ",B2) .. why? All you are doing is combining them to make a unique value. Then you do the same thing on the other page. So you have two exact unique values. This is EXTREMELY useful when you try to use combinations with dates. Dates will typically help you achieve the unique value. Let me know if this makes sense.

2. Once you have a unique value all you do is just do a standard vlookup. Once you get the hang of this you wont ever do all these formulas again in this scenario.

Example: Column 3 is the formula you can remove the " " in the formula if you do not want the space.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl539, width: 64"]column 1[/TD]
[TD="class: xl539, width: 64"]column 2[/TD]
[TD="class: xl539, width: 64"]column 3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]BC[/TD]
[TD]A BC[/TD]
[/TR]
</tbody>[/TABLE]

 

[MASOODAHMAD]

@MrExcel, I was trying to locate some video on the YouTube on this issue, but didn't find any. Do you recall any video on such thing either by you or by Mr. Mike Girvin (ExcelisFun). I want to explore more on this.

Thanks a lot for all your support over the years

 

[MASOODAHMAD]

While searching I came through 'Excel Magic Trick 1483' at https://www.youtube.com/watch?v=EMDIkP76WkU.

I tried to use the trick in my scenario and came up with this formula:
=IFERROR(""&LOOKUP(2,1/(($A$2:$A$14=E9)*($B$2:$B$14=F9)),$C$2:$C$14),"")

This however, does the job, but since LOOKUP only does the Approximate match, I am not fully confident in this formula.

 

[Aladin Akyurek]

While searching I came through 'Excel Magic Trick 1483' at https://www.youtube.com/watch?v=EMDIkP76WkU.

I tried to use the trick in my scenario and came up with this formula:
=IFERROR(""&LOOKUP(2,1/(($A$2:$A$14=E9)*($B$2:$B$14=F9)),$C$2:$C$14),"")

This however, does the job, but since LOOKUP only does the Approximate match, I am not fully confident in this formula.

See: https://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html

 

[MASOODAHMAD]

[TABLE="width: 261"]
<tbody>[TR]
[TD="class: xl63, width: 88"]
[/TD]
[TD="class: xl63, width: 88, align: center"]A
[/TD]
[TD="class: xl63, width: 172, align: center"]B
[/TD]
[TD="class: xl63, width: 88, align: center"]C
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 88"]1
[/TD]
[TD="class: xl63, width: 88"]Ecode
[/TD]
[TD="class: xl63, width: 172"]Date[/TD]
[TD="class: xl63, width: 88"]Reason[/TD]
[/TR]
[TR]
[TD="class: xl64"]2
[/TD]
[TD="class: xl64"]EKCS-1247[/TD]
[TD="class: xl65"]1/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]3
[/TD]
[TD="class: xl64"]EKCS-1141[/TD]
[TD="class: xl65"]1/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]4
[/TD]
[TD="class: xl64"]EKCS-0366[/TD]
[TD="class: xl65"]1/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]5
[/TD]
[TD="class: xl64"]EKCS-1317[/TD]
[TD="class: xl65"]1/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl66"]6
[/TD]
[TD="class: xl66"]EKCS-1247
[/TD]
[TD="class: xl67"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday
[/TD]
[/TR]
[TR]
[TD="class: xl66"]7
[/TD]
[TD="class: xl66"]EKCS-1141[/TD]
[TD="class: xl67"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]8
[/TD]
[TD="class: xl66"]EKCS-0366[/TD]
[TD="class: xl67"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl66"]9
[/TD]
[TD="class: xl66"]EKCS-1317[/TD]
[TD="class: xl67"]7/5/2018, Monday[/TD]
[TD="class: xl66"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl64"]10
[/TD]
[TD="class: xl64"]EKCS-1247[/TD]
[TD="class: xl65"]8/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl66"]11
[/TD]
[TD="class: xl66"]EKCS-1188
[/TD]
[TD="class: xl67"]25/5/2018, Friday[/TD]
[TD="class: xl66"]Holiday
[/TD]
[/TR]
[TR]
[TD="class: xl64"]12
[/TD]
[TD="class: xl64"]EKCS-0366[/TD]
[TD="class: xl65"]8/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]13
[/TD]
[TD="class: xl64"]EKCS-1317[/TD]
[TD="class: xl65"]8/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]14
[/TD]
[TD="class: xl64"]EKCS-1247[/TD]
[TD="class: xl65"]8/5/2018, Tuesday[/TD]
[TD="class: xl64"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }.xl64 { text-align: center; border: 0.5pt solid windowtext; }.xl65 { text-align: center; border: 0.5pt solid windowtext; }.xl66 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl67 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl68 { text-align: center; }</style>

[TABLE="width: 673"]
<tbody>[TR]
[TD="class: xl70, width: 88"]
[/TD]
[TD="class: xl70, width: 88, align: center"]A
[/TD]
[TD="class: xl70, width: 172, align: center"]B
[/TD]
[TD="class: xl70, width: 88, align: center"]C
[/TD]
[TD="width: 25, align: center"]D
[/TD]
[TD="width: 151, align: center"]E
[/TD]
[TD="width: 25, align: center"]F
[/TD]
[TD="class: xl69, width: 88, align: center"]G
[/TD]
[TD="class: xl69, width: 172, align: center"]H
[/TD]
[TD="class: xl69, width: 88, align: center"]I
[/TD]
[/TR]
[TR]
[TD="class: xl70, width: 88"]19
[/TD]
[TD="class: xl70, width: 88"]Ecode
[/TD]
[TD="class: xl70, width: 172"]Date[/TD]
[TD="class: xl70, width: 88"]Reason[/TD]
[TD="width: 25"][/TD]
[TD="width: 151"][/TD]
[TD="width: 25"][/TD]
[TD="class: xl69, width: 88"]Ecode[/TD]
[TD="class: xl69, width: 172"]Date[/TD]
[TD="class: xl69, width: 88"]Reason[/TD]
[/TR]
[TR]
[TD="class: xl71"]20
[/TD]
[TD="class: xl71"]EKCS-0123[/TD]
[TD="class: xl72"]2/6/2018, Saturday[/TD]
[TD="class: xl71"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-0123[/TD]
[TD="class: xl64, align: right"]2/6/2018, Saturday[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl71"]21
[/TD]
[TD="class: xl71"]EKCS-1246[/TD]
[TD="class: xl72"]13/5/2018, Sunday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1246[/TD]
[TD="class: xl64, align: right"]13/5/2018, Sunday[/TD]
[TD="class: xl63"]Absent[/TD]
[/TR]
[TR]
[TD="class: xl71"]22
[/TD]
[TD="class: xl71"]EKCS-0482[/TD]
[TD="class: xl72"]21/5/2018, Monday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-0482[/TD]
[TD="class: xl64, align: right"]21/5/2018, Monday[/TD]
[TD="class: xl63"]Comp-OFF[/TD]
[/TR]
[TR]
[TD="class: xl71"]23
[/TD]
[TD="class: xl71"]EKCS-0229[/TD]
[TD="class: xl72"]29/5/2018, Tuesday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-0229[/TD]
[TD="class: xl64, align: right"]29/5/2018, Tuesday[/TD]
[TD="class: xl63"]Absent[/TD]
[/TR]
[TR]
[TD="class: xl71"]24
[/TD]
[TD="class: xl71"]EKCS-0362[/TD]
[TD="class: xl72"]15/5/2018, Tuesday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0362[/TD]
[TD="class: xl64, align: right"]15/5/2018, Tuesday[/TD]
[TD="class: xl63"]Comp-OFF[/TD]
[/TR]
[TR]
[TD="class: xl71"]25
[/TD]
[TD="class: xl71"]EKCS-0366[/TD]
[TD="class: xl72"]8/5/2018, Tuesday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"]Absent[/TD]
[/TR]
[TR]
[TD="class: xl73"]26
[/TD]
[TD="class: xl73"]EKCS-1317
[/TD]
[TD="class: xl74"]7/5/2018, Monday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl65"]EKCS-1317[/TD]
[TD="class: xl66, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl65"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl71"]27
[/TD]
[TD="class: xl71"]EKCS-1317[/TD]
[TD="class: xl72"]8/5/2018, Tuesday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"]Absent[/TD]
[/TR]
[TR]
[TD="class: xl71"]28
[/TD]
[TD="class: xl71"]EKCS-1062[/TD]
[TD="class: xl72"]23/5/2018, Wednesday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-1062[/TD]
[TD="class: xl64, align: right"]23/5/2018, Wednesday[/TD]
[TD="class: xl63"]Comp-OFF[/TD]
[/TR]
[TR]
[TD="class: xl71"]29
[/TD]
[TD="class: xl71"]EKCS-1247[/TD]
[TD="class: xl72"]8/5/2018, Tuesday[/TD]
[TD="class: xl71"]Leave[/TD]
[TD][/TD]
[TD="class: xl76"]OUTPUT >>>[/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday[/TD]
[TD="class: xl63"]Leave[/TD]
[/TR]
[TR]
[TD="class: xl73"]30
[/TD]
[TD="class: xl73"]EKCS-1188
[/TD]
[TD="class: xl74"]25/5/2018, Friday[/TD]
[TD="class: xl71"]Leave[/TD]
[TD]
[/TD]
[TD="class: xl77"]Merged Table[/TD]
[TD]
[/TD]
[TD="class: xl68"]EKCS-1188[/TD]
[TD="class: xl66, align: right"]25/5/2018, Friday[/TD]
[TD="class: xl65"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl71"]31[/TD]
[TD="class: xl71"]EKCS-0683[/TD]
[TD="class: xl72"]31/5/2018, Thursday[/TD]
[TD="class: xl71"]Leave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-0683[/TD]
[TD="class: xl64, align: right"]31/5/2018, Thursday[/TD]
[TD="class: xl63"]Leave[/TD]
[/TR]
[TR]
[TD="class: xl71"]32[/TD]
[TD="class: xl71"]EKCS-1306[/TD]
[TD="class: xl72"]6/6/2018, Wednesday[/TD]
[TD="class: xl71"]Leave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-1306[/TD]
[TD="class: xl64, align: right"]6/6/2018, Wednesday[/TD]
[TD="class: xl63"]Leave[/TD]
[/TR]
[TR]
[TD="class: xl71"]33[/TD]
[TD="class: xl71"]EKCS-0366[/TD]
[TD="class: xl72"]1/5/2018, Tuesday[/TD]
[TD="class: xl71"]Leave
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0366[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"]Leave[/TD]
[/TR]
[TR]
[TD="class: xl71"]34[/TD]
[TD="class: xl71"]EKCS-1247[/TD]
[TD="class: xl72"]8/5/2018, Tuesday[/TD]
[TD="class: xl71"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]8/5/2018, Tuesday
[/TD]
[TD="class: xl63"]Leave
[/TD]
[/TR]
[TR]
[TD="class: xl73"]35[/TD]
[TD="class: xl73"]EKCS-1141
[/TD]
[TD="class: xl74"]7/5/2018, Monday[/TD]
[TD="class: xl71"]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl65"]EKCS-1141
[/TD]
[TD="class: xl66, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl65"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl71"]36
[/TD]
[TD="class: xl71"]EKCS-0331[/TD]
[TD="class: xl72"]9/5/2018, Wednesday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-0331[/TD]
[TD="class: xl64, align: right"]9/5/2018, Wednesday[/TD]
[TD="class: xl63"]Absent[/TD]
[/TR]
[TR]
[TD="class: xl71"]37
[/TD]
[TD="class: xl71"]EKCS-1358[/TD]
[TD="class: xl72"]11/5/2018, Friday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-1358[/TD]
[TD="class: xl64, align: right"]11/5/2018, Friday[/TD]
[TD="class: xl63"]Comp-OFF[/TD]
[/TR]
[TR]
[TD="class: xl71"]38
[/TD]
[TD="class: xl71"]EKCS-1235[/TD]
[TD="class: xl72"]4/6/2018, Monday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-1235[/TD]
[TD="class: xl64, align: right"]4/6/2018, Monday[/TD]
[TD="class: xl63"]Absent[/TD]
[/TR]
[TR]
[TD="class: xl71"]39
[/TD]
[TD="class: xl71"]EKCS-1317[/TD]
[TD="class: xl72"]1/5/2018, Tuesday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-1317[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"]Comp-OFF[/TD]
[/TR]
[TR]
[TD="class: xl71"]40
[/TD]
[TD="class: xl71"]EKCS-1247[/TD]
[TD="class: xl72"]3/5/2018, Thursday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-1247[/TD]
[TD="class: xl64, align: right"]3/5/2018, Thursday[/TD]
[TD="class: xl63"]Absent[/TD]
[/TR]
[TR]
[TD="class: xl71"]41
[/TD]
[TD="class: xl71"]EKCS-1141[/TD]
[TD="class: xl72"]1/5/2018, Tuesday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-1141[/TD]
[TD="class: xl64, align: right"]1/5/2018, Tuesday[/TD]
[TD="class: xl63"]Comp-OFF
[/TD]
[/TR]
[TR]
[TD="class: xl73"]42
[/TD]
[TD="class: xl73"]EKCS-0366
[/TD]
[TD="class: xl74"]7/5/2018, Monday[/TD]
[TD="class: xl71"]Absent[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl65"]EKCS-0366[/TD]
[TD="class: xl66, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl65"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl73"]43
[/TD]
[TD="class: xl73"]EKCS-1247
[/TD]
[TD="class: xl74"]7/5/2018, Monday[/TD]
[TD="class: xl71"]Comp-OFF[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="class: xl65"]EKCS-1247[/TD]
[TD="class: xl66, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl65"]Holiday[/TD]
[/TR]
[TR]
[TD="class: xl71"]44
[/TD]
[TD="class: xl71"]EKCS-0949[/TD]
[TD="class: xl72"]7/5/2018, Monday[/TD]
[TD="class: xl71"]Leave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"]EKCS-0949[/TD]
[TD="class: xl64, align: right"]7/5/2018, Monday[/TD]
[TD="class: xl63"]Leave[/TD]
[/TR]
[TR]
[TD="class: xl71"]45
[/TD]
[TD="class: xl71"]EKCS-0911[/TD]
[TD="class: xl72"]27/5/2018, Sunday[/TD]
[TD="class: xl71"]ML[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]EKCS-0911[/TD]
[TD="class: xl64, align: right"]27/5/2018, Sunday[/TD]
[TD="class: xl63"]ML[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { border: 0.5pt solid windowtext; }.xl65 { text-align: left; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl66 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl67 { border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl69 { font-weight: 700; border: 0.5pt solid windowtext; }.xl70 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }.xl71 { text-align: center; border: 0.5pt solid windowtext; }.xl72 { text-align: center; border: 0.5pt solid windowtext; }.xl73 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl74 { text-align: center; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl75 { text-align: center; }.xl76 { border-color: windowtext windowtext currentcolor; border-style: solid solid none; border-width: 0.5pt 0.5pt medium; }.xl77 { border-color: currentcolor windowtext windowtext; border-style: none solid solid; border-width: medium 0.5pt 0.5pt; }</style>

With the help of formulas from @Marcelo Branco and @kvsrinivasamurthy, I managed to merge two tables and get the combined values in Column I.

I use this Array formula in I20 to I45:
=IF(COUNTIFS($A$2:$A$14,G20,$B$2:$B$14,H20,$C$2:$C$14,"Holiday")>=1,"Holiday",""&INDEX($C$20:$C$45,MATCH(1,IF($A$20:$A$45=G20,IF($B$20:$B$45=H20,1)),0)))

Results can easily be identified in Column I, Row 26, 30, 35, 42 and 43 (in RED).


However, when I tried to use the same approach in table with multiple lookup columns, I'm not getting the desired results.

Here is the scenario:

I have Table-A with varied codes for each employee for a month (in the example I have taken data for 10 days only). This sheet is filled in by Person A.

There is another Person B, who fills the same table with 'L' codes for each employee as shown in Table-B.

All the two tables (Table-A and Table-B) needs to be merged together. The 'L' dominates over all the codes, means that if a cell value is 'L' in Table-B, then 'L', else take the code from Table-A.


Table-A
[TABLE="width: 590"]
<tbody>[TR]
[TD="class: xl64, width: 80, align: center"]
[/TD]
[TD="class: xl64, width: 80, align: center"]A
[/TD]
[TD="class: xl65, width: 71, align: center"]B
[/TD]
[TD="class: xl65, width: 71, align: center"]C
[/TD]
[TD="class: xl65, width: 71, align: center"]D
[/TD]
[TD="class: xl65, width: 71, align: center"]E
[/TD]
[TD="class: xl65, width: 71, align: center"]F
[/TD]
[TD="class: xl65, width: 71, align: center"]G
[/TD]
[TD="class: xl65, width: 71, align: center"]H
[/TD]
[TD="class: xl65, width: 71, align: center"]I
[/TD]
[TD="class: xl65, width: 71, align: center"]J
[/TD]
[TD="class: xl65, width: 71, align: center"]K
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 80"]1
[/TD]
[TD="class: xl64, width: 80"]Ecode[/TD]
[TD="class: xl65, width: 71, align: right"]1-May[/TD]
[TD="class: xl65, width: 71, align: right"]2-May[/TD]
[TD="class: xl65, width: 71, align: right"]3-May[/TD]
[TD="class: xl65, width: 71, align: right"]4-May[/TD]
[TD="class: xl65, width: 71, align: right"]5-May[/TD]
[TD="class: xl65, width: 71, align: right"]6-May[/TD]
[TD="class: xl65, width: 71, align: right"]7-May[/TD]
[TD="class: xl65, width: 71, align: right"]8-May[/TD]
[TD="class: xl65, width: 71, align: right"]9-May[/TD]
[TD="class: xl65, width: 71, align: right"]10-May[/TD]
[/TR]
[TR]
[TD="class: xl63"]2
[/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]E[/TD]
[TD="class: xl66"]F[/TD]
[TD="class: xl66"]G[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]I[/TD]
[TD="class: xl66"]J[/TD]
[/TR]
[TR]
[TD="class: xl63"]3
[/TD]
[TD="class: xl63"]EKCS-1248[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]E[/TD]
[TD="class: xl66"]F[/TD]
[TD="class: xl66"]G[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]M[/TD]
[TD="class: xl66"]N[/TD]
[/TR]
[TR]
[TD="class: xl63"]4
[/TD]
[TD="class: xl63"]EKCS-1249[/TD]
[TD="class: xl66"]G[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]M[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]I[/TD]
[TD="class: xl66"]J[/TD]
[TD="class: xl66"]P[/TD]
[/TR]
[TR]
[TD="class: xl63"]5
[/TD]
[TD="class: xl63"]EKCS-1250[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]M[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]I[/TD]
[TD="class: xl66"]J[/TD]
[TD="class: xl66"]P[/TD]
[TD="class: xl66"]H[/TD]
[/TR]
[TR]
[TD="class: xl63"]6
[/TD]
[TD="class: xl63"]EKCS-1251[/TD]
[TD="class: xl66"]M[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]I[/TD]
[TD="class: xl66"]J[/TD]
[TD="class: xl66"]P[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]E[/TD]
[/TR]
[TR]
[TD="class: xl63"]7
[/TD]
[TD="class: xl63"]EKCS-1252[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]E[/TD]
[TD="class: xl66"]F[/TD]
[TD="class: xl66"]G[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]M[/TD]
[/TR]
[TR]
[TD="class: xl63"]8
[/TD]
[TD="class: xl63"]EKCS-1253[/TD]
[TD="class: xl66"]P[/TD]
[TD="class: xl66"]I[/TD]
[TD="class: xl66"]J[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]M[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]D[/TD]
[/TR]
[TR]
[TD="class: xl63"]9
[/TD]
[TD="class: xl63"]EKCS-1254[/TD]
[TD="class: xl66"]E[/TD]
[TD="class: xl66"]F[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]G[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]K[/TD]
[TD="class: xl66"]M[/TD]
[TD="class: xl66"]N[/TD]
[TD="class: xl66"]O[/TD]
[/TR]
[TR]
[TD="class: xl63"]10
[/TD]
[TD="class: xl63"]EKCS-1255[/TD]
[TD="class: xl66"]J[/TD]
[TD="class: xl66"]P[/TD]
[TD="class: xl66"]H[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl66"]D[/TD]
[TD="class: xl66"]E[/TD]
[TD="class: xl66"]F[/TD]
[TD="class: xl66"]G[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { font-weight: 700; border: 0.5pt solid windowtext; }.xl65 { border: 0.5pt solid windowtext; }.xl66 { text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; }</style>

Table-B
[TABLE="width: 590"]
<tbody>[TR]
[TD="class: xl64, width: 80, align: center"]
[/TD]
[TD="class: xl64, width: 80, align: center"]A
[/TD]
[TD="class: xl65, width: 71, align: center"]B
[/TD]
[TD="class: xl65, width: 71, align: center"]C
[/TD]
[TD="class: xl65, width: 71, align: center"]D
[/TD]
[TD="class: xl65, width: 71, align: center"]E
[/TD]
[TD="class: xl65, width: 71, align: center"]F
[/TD]
[TD="class: xl65, width: 71, align: center"]G
[/TD]
[TD="class: xl65, width: 71, align: center"]H
[/TD]
[TD="class: xl65, width: 71, align: center"]I
[/TD]
[TD="class: xl65, width: 71, align: center"]J
[/TD]
[TD="class: xl65, width: 71, align: center"]K
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 80"]1
[/TD]
[TD="class: xl64, width: 80"]Ecode[/TD]
[TD="class: xl65, width: 71, align: right"]1-May[/TD]
[TD="class: xl65, width: 71, align: right"]2-May[/TD]
[TD="class: xl65, width: 71, align: right"]3-May[/TD]
[TD="class: xl65, width: 71, align: right"]4-May[/TD]
[TD="class: xl65, width: 71, align: right"]5-May[/TD]
[TD="class: xl65, width: 71, align: right"]6-May[/TD]
[TD="class: xl65, width: 71, align: right"]7-May[/TD]
[TD="class: xl65, width: 71, align: right"]8-May[/TD]
[TD="class: xl65, width: 71, align: right"]9-May[/TD]
[TD="class: xl65, width: 71, align: right"]10-May[/TD]
[/TR]
[TR]
[TD="class: xl63"]2
[/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]3
[/TD]
[TD="class: xl63"]EKCS-1248[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]4
[/TD]
[TD="class: xl63"]EKCS-1249[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]5
[/TD]
[TD="class: xl63"]EKCS-1250[/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]6
[/TD]
[TD="class: xl63"]EKCS-1251[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]7
[/TD]
[TD="class: xl63"]EKCS-1252[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]8
[/TD]
[TD="class: xl63"]EKCS-1253[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]9
[/TD]
[TD="class: xl63"]EKCS-1254[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl63"]10
[/TD]
[TD="class: xl63"]EKCS-1255[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]L[/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { font-weight: 700; border: 0.5pt solid windowtext; }.xl65 { border: 0.5pt solid windowtext; }.xl66 { text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; }.xl67 { text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }</style>
NOTE: These 'L's from the above table needs to be merged with Table-A


Table-C (Merge)
[TABLE="width: 590"]
<tbody>[TR]
[TD="class: xl64, width: 80, align: center"]
[/TD]
[TD="class: xl64, width: 80, align: center"]A
[/TD]
[TD="class: xl67, width: 71, align: center"]B
[/TD]
[TD="class: xl67, width: 71, align: center"]C
[/TD]
[TD="class: xl67, width: 71, align: center"]D
[/TD]
[TD="class: xl67, width: 71, align: center"]E
[/TD]
[TD="class: xl67, width: 71, align: center"]F
[/TD]
[TD="class: xl67, width: 71, align: center"]G
[/TD]
[TD="class: xl67, width: 71, align: center"]H
[/TD]
[TD="class: xl67, width: 71, align: center"]I
[/TD]
[TD="class: xl67, width: 71, align: center"]J
[/TD]
[TD="class: xl67, width: 71, align: center"]K
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 80"]1
[/TD]
[TD="class: xl64, width: 80"]Ecode[/TD]
[TD="class: xl67, width: 71, align: right"]1-May[/TD]
[TD="class: xl67, width: 71, align: right"]2-May[/TD]
[TD="class: xl67, width: 71, align: right"]3-May[/TD]
[TD="class: xl67, width: 71, align: right"]4-May[/TD]
[TD="class: xl67, width: 71, align: right"]5-May[/TD]
[TD="class: xl67, width: 71, align: right"]6-May[/TD]
[TD="class: xl67, width: 71, align: right"]7-May[/TD]
[TD="class: xl67, width: 71, align: right"]8-May[/TD]
[TD="class: xl67, width: 71, align: right"]9-May[/TD]
[TD="class: xl67, width: 71, align: right"]10-May[/TD]
[/TR]
[TR]
[TD="class: xl63"]2
[/TD]
[TD="class: xl63"]EKCS-1247[/TD]
[TD="class: xl66"]L
[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"]E[/TD]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"]I[/TD]
[TD="class: xl65"]J[/TD]
[/TR]
[TR]
[TD="class: xl63"]3
[/TD]
[TD="class: xl63"]EKCS-1248[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"]E[/TD]
[TD="class: xl66"]L
[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[/TR]
[TR]
[TD="class: xl63"]4
[/TD]
[TD="class: xl63"]EKCS-1249[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl66"]L
[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]O[/TD]
[TD="class: xl65"]I[/TD]
[TD="class: xl65"]J[/TD]
[TD="class: xl65"]P[/TD]
[/TR]
[TR]
[TD="class: xl63"]5
[/TD]
[TD="class: xl63"]EKCS-1250[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]O[/TD]
[TD="class: xl65"]I[/TD]
[TD="class: xl65"]J[/TD]
[TD="class: xl65"]P[/TD]
[TD="class: xl65"]H[/TD]
[/TR]
[TR]
[TD="class: xl63"]6
[/TD]
[TD="class: xl63"]EKCS-1251[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]O[/TD]
[TD="class: xl65"]I[/TD]
[TD="class: xl65"]J[/TD]
[TD="class: xl65"]P[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"]E[/TD]
[/TR]
[TR]
[TD="class: xl63"]7
[/TD]
[TD="class: xl63"]EKCS-1252[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"]E[/TD]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl66"]L
[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]M[/TD]
[/TR]
[TR]
[TD="class: xl63"]8
[/TD]
[TD="class: xl63"]EKCS-1253[/TD]
[TD="class: xl65"]P[/TD]
[TD="class: xl65"]I[/TD]
[TD="class: xl65"]J[/TD]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[/TR]
[TR]
[TD="class: xl63"]9
[/TD]
[TD="class: xl63"]EKCS-1254[/TD]
[TD="class: xl65"]E[/TD]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]O[/TD]
[/TR]
[TR]
[TD="class: xl63"]10
[/TD]
[TD="class: xl63"]EKCS-1255[/TD]
[TD="class: xl65"]J[/TD]
[TD="class: xl65"]P[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"]E[/TD]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl66"]L[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: left; border: 0.5pt solid windowtext; }.xl64 { font-weight: 700; border: 0.5pt solid windowtext; }.xl65 { text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; }.xl66 { text-align: center; vertical-align: middle; border: 0.5pt solid windowtext; background: yellow none repeat scroll 0% 0%; }.xl67 { font-weight: 700; border: 0.5pt solid windowtext; }</style>

Any help would be much appreciated.

 

[Marcelo Branco]

As the three tables have the same number of rows and columns you can use a simple formula:

B2 of Sheet3 copied across and down
=IF(INDEX(Sheet2!$A$1:$K$10,ROWS(B$2:B2)+1,COLUMNS($B2:B2)+1)="L","L",INDEX(Sheet1!$A$1:$K$10,ROWS(B$2:B2)+1,COLUMNS($B2:B2)+1))

Assumes Table-A in Sheet1; Table-B in Sheet2; Table-C in Sheet3

M.

 

[MASOODAHMAD]

Hi Marcelo,

Thanks again for your inputs. Your formula did worked if the tables are identical, but don't if they are not. The Employee code in Column A could be in Random order, therefore, needs something which will look and verify the Employee Code and date from the three table and then come up with the results.

Just like we did in the previous scenario by matching to conditions using IF.