Lookup if two conditions are met!

M

MASOODAHMAD

Board Regular
Joined
Mar 28, 2012
Messages
105
Platform
  1. MacOS
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.
 
Last edited:
Sort by date Sort by votes
MASOODAHMAD said:
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.
Click to expand...

Ok, then you should use INDEX(table,match,match)
Something like
B2 copied across and down
=IF(INDEX(Sheet2!$A$1:$K$10,MATCH($A2,Sheet2!$A$1:$A$10,0),MATCH(B$1,Sheet2!$A$1:$K$1,0))="L","L",INDEX(Sheet1!$A$1:$K$10,MATCH($A2,Sheet1!$A$1:$A$10,0),MATCH(B$1,Sheet1!$A$1:$K$1,0)))

M.
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You're a genius Marcelo,

I think the formula worked, I'll test it further by tomorrow.

Thanks for all your support.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
Collington
  • Question Question
Creating a daily register based on a different worksheet
Replies
3
Views
594
Fluff
Fluff
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
A
  • Question Question
Countif formula help
Replies
1
Views
585
jorismoerings
J

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top