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:
EcodeDateDay
EKCS-12471/5/2018, Tuesday
EKCS-11411/5/2018, Tuesday
EKCS-03661/5/2018, Tuesday
EKCS-13171/5/2018, Tuesday
EKCS-1247
7/5/2018, Monday
Holiday
EKCS-11417/5/2018, Monday
Holiday
EKCS-03667/5/2018, Monday
Holiday
EKCS-13177/5/2018, MondayHoliday
EKCS-12478/5/2018, Tuesday
EKCS-11418/5/2018, Tuesday
EKCS-03668/5/2018, Tuesday
EKCS-13178/5/2018, Tuesday
EKCS-12478/5/2018, Tuesday

<tbody>
</tbody>
<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:
EcodeDateDayEcodeDateDay
EKCS-01232/6/2018, SaturdayEKCS-01232/6/2018, Saturday
EKCS-124613/5/2018, SundayEKCS-124613/5/2018, Sunday
EKCS-048221/5/2018, MondayEKCS-048221/5/2018, Monday
EKCS-022929/5/2018, TuesdayEKCS-022929/5/2018, Tuesday
EKCS-036215/5/2018, TuesdayEKCS-036215/5/2018, Tuesday
EKCS-03668/5/2018, TuesdayEKCS-03668/5/2018, Tuesday
EKCS-1317
7/5/2018, Monday



EKCS-13177/5/2018, MondayHoliday
EKCS-13178/5/2018, TuesdayEKCS-13178/5/2018, Tuesday
EKCS-106223/5/2018, WednesdayEKCS-106223/5/2018, Wednesday
EKCS-12478/5/2018, TuesdayEKCS-12478/5/2018, Tuesday
EKCS-118825/5/2018, FridayEKCS-118825/5/2018, Friday
EKCS-068331/5/2018, ThursdayOUTPUT >>EKCS-068331/5/2018, Thursday
EKCS-13066/6/2018, WednesdayEKCS-13066/6/2018, Wednesday
EKCS-03661/5/2018, TuesdayEKCS-03661/5/2018, Tuesday
EKCS-12478/5/2018, TuesdayEKCS-12478/5/2018, Tuesday
EKCS-1141
7/5/2018, Monday



EKCS-11417/5/2018, MondayHoliday
EKCS-03319/5/2018, WednesdayEKCS-03319/5/2018, Wednesday
EKCS-135811/5/2018, FridayEKCS-135811/5/2018, Friday
EKCS-12354/6/2018, MondayEKCS-12354/6/2018, Monday
EKCS-13171/5/2018, TuesdayEKCS-13171/5/2018, Tuesday
EKCS-12473/5/2018, ThursdayEKCS-12473/5/2018, Thursday
EKCS-11411/5/2018, TuesdayEKCS-11411/5/2018, Tuesday
EKCS-0366
7/5/2018, Monday



EKCS-03667/5/2018, MondayHoliday
EKCS-12477/5/2018, Monday



EKCS-12477/5/2018, MondayHoliday
EKCS-09497/5/2018, MondayEKCS-09497/5/2018, Monday
EKCS-091127/5/2018, SundayEKCS-091127/5/2018, Sunday

<tbody>
</tbody>
<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
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.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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.

column 1column 2column 3
ABCA BC

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
@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 :)
 
Upvote 0
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.
 
Upvote 0
Upvote 0

A
B
C
1
Ecode
DateReason
2
EKCS-12471/5/2018, Tuesday
3
EKCS-11411/5/2018, Tuesday
4
EKCS-03661/5/2018, Tuesday
5
EKCS-13171/5/2018, Tuesday
6
EKCS-1247
7/5/2018, MondayHoliday
7
EKCS-11417/5/2018, MondayHoliday
8
EKCS-03667/5/2018, MondayHoliday
9
EKCS-13177/5/2018, MondayHoliday
10
EKCS-12478/5/2018, Tuesday
11
EKCS-1188
25/5/2018, FridayHoliday
12
EKCS-03668/5/2018, Tuesday
13
EKCS-13178/5/2018, Tuesday
14
EKCS-12478/5/2018, Tuesday

<tbody>
</tbody>
<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>


A
B
C
D
E
F
G
H
I
19
Ecode
DateReasonEcodeDateReason
20
EKCS-01232/6/2018, SaturdayEKCS-01232/6/2018, Saturday
21
EKCS-124613/5/2018, SundayAbsentEKCS-124613/5/2018, SundayAbsent
22
EKCS-048221/5/2018, MondayComp-OFFEKCS-048221/5/2018, MondayComp-OFF
23
EKCS-022929/5/2018, TuesdayAbsentEKCS-022929/5/2018, TuesdayAbsent
24
EKCS-036215/5/2018, TuesdayComp-OFFEKCS-036215/5/2018, TuesdayComp-OFF
25
EKCS-03668/5/2018, TuesdayAbsentEKCS-03668/5/2018, TuesdayAbsent
26
EKCS-1317
7/5/2018, MondayComp-OFF


EKCS-13177/5/2018, MondayHoliday
27
EKCS-13178/5/2018, TuesdayAbsentEKCS-13178/5/2018, TuesdayAbsent
28
EKCS-106223/5/2018, WednesdayComp-OFFEKCS-106223/5/2018, WednesdayComp-OFF
29
EKCS-12478/5/2018, TuesdayLeaveOUTPUT >>>EKCS-12478/5/2018, TuesdayLeave
30
EKCS-1188
25/5/2018, FridayLeave
Merged Table
EKCS-118825/5/2018, FridayHoliday
31EKCS-068331/5/2018, ThursdayLeaveEKCS-068331/5/2018, ThursdayLeave
32EKCS-13066/6/2018, WednesdayLeaveEKCS-13066/6/2018, WednesdayLeave
33EKCS-03661/5/2018, TuesdayLeave
EKCS-03661/5/2018, TuesdayLeave
34EKCS-12478/5/2018, TuesdayEKCS-12478/5/2018, Tuesday
Leave
35EKCS-1141
7/5/2018, Monday



EKCS-1141
7/5/2018, MondayHoliday
36
EKCS-03319/5/2018, WednesdayAbsentEKCS-03319/5/2018, WednesdayAbsent
37
EKCS-135811/5/2018, FridayComp-OFFEKCS-135811/5/2018, FridayComp-OFF
38
EKCS-12354/6/2018, MondayAbsentEKCS-12354/6/2018, MondayAbsent
39
EKCS-13171/5/2018, TuesdayComp-OFFEKCS-13171/5/2018, TuesdayComp-OFF
40
EKCS-12473/5/2018, ThursdayAbsentEKCS-12473/5/2018, ThursdayAbsent
41
EKCS-11411/5/2018, TuesdayComp-OFFEKCS-11411/5/2018, TuesdayComp-OFF
42
EKCS-0366
7/5/2018, MondayAbsent


EKCS-03667/5/2018, MondayHoliday
43
EKCS-1247
7/5/2018, MondayComp-OFF


EKCS-12477/5/2018, MondayHoliday
44
EKCS-09497/5/2018, MondayLeaveEKCS-09497/5/2018, MondayLeave
45
EKCS-091127/5/2018, SundayMLEKCS-091127/5/2018, SundayML

<tbody>
</tbody>
<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

A
B
C
D
E
F
G
H
I
J
K
1
Ecode1-May2-May3-May4-May5-May6-May7-May8-May9-May10-May
2
EKCS-1247ABCDEFGHIJ
3
EKCS-1248CDEFGABKMN
4
EKCS-1249GABKMNOIJP
5
EKCS-1250ABKMNOIJPH
6
EKCS-1251MNOIJPHCDE
7
EKCS-1252HCDEFGABKM
8
EKCS-1253PIJKMNABCD
9
EKCS-1254EFHGABKMNO
10
EKCS-1255JPHCDEFGAB

<tbody>
</tbody>
<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

A
B
C
D
E
F
G
H
I
J
K
1
Ecode1-May2-May3-May4-May5-May6-May7-May8-May9-May10-May
2
EKCS-1247L
3
EKCS-1248LLL
4
EKCS-1249L
5
EKCS-1250L
6
EKCS-1251
7
EKCS-1252L
8
EKCS-1253L
9
EKCS-1254L
10
EKCS-1255L

<tbody>
</tbody>
<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)

A
B
C
D
E
F
G
H
I
J
K
1
Ecode1-May2-May3-May4-May5-May6-May7-May8-May9-May10-May
2
EKCS-1247L
BCDEFGHIJ
3
EKCS-1248CDEL
LLBKMN
4
EKCS-1249GL
BKMNOIJP
5
EKCS-1250LBKMNOIJPH
6
EKCS-1251MNOIJPHCDE
7
EKCS-1252HCDEFGL
BKM
8
EKCS-1253PIJKMNLBCD
9
EKCS-1254EFHGLBKMNO
10
EKCS-1255JPHCDEFGLB

<tbody>
</tbody>
<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.
 
Upvote 0
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.
 
Upvote 0
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.
 
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
377
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
Collington
  • Question
Creating a daily register based on a different worksheet
Replies
3
Views
514
Fluff
Fluff
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
A
  • Question
Countif formula help
Replies
1
Views
494
jorismoerings
J

Forum statistics

Threads
1,214,430
Messages
6,119,447
Members
448,898
Latest member
drewmorgan128

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