How to achieve this? Vlookup limitation with results

C

Cferron

New Member
Joined
May 20, 2011
Messages
44
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Hello to all,

As I have read around, Vlookup only show first or last results depending if FALSE or TRUE variable is used in the formula.
Here the issue I'm trying to solve.
In column A & B I have data. A shows employee number, B a working date.
I want screen A:B (using a function?) and return the values in the table on the right.
If employee number and a date is found in the list (A:B) it returns TRUE, otherwise FALSE.

I was thinking VLookup could do that.
Any help is much appreciated!

Thanks

Claude
1642132915674.png
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Here is a minisheet:
Book1
ABCDEFGHIJKL
1No empl.DateNO EMPLOYÉvendredi, 14 janviersamedi, 15 janvierdimanche, 16 janvierlundi, 17 janviermardi, 18 janviermercredi, 19 janvierjeudi, 20 janvier
2640749samedi, 15 janvier640749TRUETRUETRUETRUEFALSEFALSEFALSE
3640749dimanche, 16 janvier000044FALSEFALSETRUEFALSEFALSETRUEFALSE
4640749lundi, 17 janvier301884FALSEFALSEFALSEFALSEFALSEFALSEFALSE
5000044dimanche, 16 janvier640010FALSEFALSEFALSEFALSEFALSEFALSEFALSE
6000044mercredi, 19 janvier623197FALSEFALSEFALSEFALSEFALSEFALSEFALSE
7000044dimanche, 16 janvier632345FALSEFALSEFALSEFALSEFALSEFALSEFALSE
8000044mercredi, 19 janvier633689FALSEFALSEFALSEFALSEFALSEFALSEFALSE
9001222vendredi, 21 janvier751432FALSEFALSEFALSEFALSEFALSEFALSEFALSE
10640749vendredi, 14 janvier640760FALSEFALSEFALSEFALSEFALSEFALSEFALSE
11001478jeudi, 20 janvier105598FALSEFALSEFALSEFALSEFALSEFALSEFALSE
12001478samedi, 22 janvier52076FALSEFALSEFALSEFALSEFALSEFALSEFALSE
13001478samedi, 22 janvier301879FALSEFALSEFALSEFALSEFALSEFALSEFALSE
14001478jeudi, 20 janvier513802FALSEFALSEFALSEFALSEFALSEFALSEFALSE
15001667jeudi, 20 janvier422230FALSEFALSEFALSEFALSEFALSEFALSEFALSE
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2,E4:E15Cell ValueduplicatestextNO
 
Upvote 0
Here is one way of doing it.

20220114 Lookup critieria in 2 dimensions.xlsx
ABCDEFGHIJKL
1No empl.DateNO EMPLOYÉFriday, 14 January 2022Saturday, 15 January 2022Sunday, 16 January 2022Monday, 17 January 2022Tuesday, 18 January 2022Wednesday, 19 January 2022Thursday, 20 January 2022
2640749Saturday, 15 January 2022640749TRUETRUETRUETRUEFALSEFALSEFALSE
3640749Sunday, 16 January 202244FALSEFALSETRUEFALSEFALSETRUEFALSE
4640749Monday, 17 January 2022301884FALSEFALSEFALSEFALSEFALSEFALSEFALSE
544Sunday, 16 January 2022640010FALSEFALSEFALSEFALSEFALSEFALSEFALSE
644Wednesday, 19 January 2022623197FALSEFALSEFALSEFALSEFALSEFALSEFALSE
744Sunday, 16 January 2022632345FALSEFALSEFALSEFALSEFALSEFALSEFALSE
844Wednesday, 19 January 2022633689FALSEFALSEFALSEFALSEFALSEFALSEFALSE
91222Friday, 21 January 2022751432FALSEFALSEFALSEFALSEFALSEFALSEFALSE
10640749Friday, 14 January 2022640760FALSEFALSEFALSEFALSEFALSEFALSEFALSE
111478Thursday, 20 January 2022105598FALSEFALSEFALSEFALSEFALSEFALSEFALSE
121478Saturday, 22 January 202252076FALSEFALSEFALSEFALSEFALSEFALSEFALSE
131478Saturday, 22 January 2022301879FALSEFALSEFALSEFALSEFALSEFALSEFALSE
141478Thursday, 20 January 2022513802FALSEFALSEFALSEFALSEFALSEFALSEFALSE
151667Thursday, 20 January 2022422230FALSEFALSEFALSEFALSEFALSEFALSEFALSE
Formula
Cell Formulas
RangeFormula
F2:L15F2=COUNTIFS($A$2:$A$15,$E2,$B$2:$B$15,F$1)>0
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E15Cell ValueduplicatestextNO
F2:L15Expression=F2=FALSEtextNO
F2:L15Expression="F2=TRUE"textNO
 
Upvote 0
Solution
Thanks so much Alex! This worked perfectly!
Thanks for taking the time!

Claude
 
Upvote 0
I reposting here again since the issue is a bit similar. I must ad a condition and I'm not sure how to deal with this :(
Basically if "ND" shows in column C for a specific employee on a specific date, I have to show "ND". Otherwise I go with the Countifs solution provided initially...

MrExcel.xlsx
ABCDEFGHIJKLMNOP
1No empl.DateCode hor.
20000441/19/22VACSH
30000441/20/22VACSHNo. empl1/19/221/20/221/21/221/22/221/23/221/24/221/25/221/26/221/27/221/28/221/29/22
40000441/20/22COV1544WORKINGNDWORKINGWORKINGFREEWORKINGWORKINGWORKINGFREEFREEWORKING
50000441/20/22ND559FREEWORKINGWORKINGFREEFREEWORKINGWORKINGFREEWORKINGWORKINGFREE
60000441/21/22VACST792FREEFREEFREEFREEFREEFREEFREEFREEWORKINGFREEFREE
70000441/22/22VACTR1222NDFREEWORKINGFREENDFREENDNDNDNDFREE
80000441/24/22VACST
90000441/25/22VACSTIf "ND" shows up in column C = Show ND
100000441/26/22VACST
110000441/29/22VACTRIf not present check if in columns A & B if countifs returns > 0
120005591/17/22J
130005591/18/22J
140005591/20/22J
150005591/21/22J
160005591/24/22J
170005591/25/22J
180005591/27/22J
190005591/28/22J
200007921/27/22FcS
210012221/19/22ND
220012221/21/22VACTR
230012221/23/22ND
240012221/25/22ND
250012221/26/22ND
260012221/27/22ND
270012221/28/22ND
Sheet1
Cell Formulas
RangeFormula
F4,P4:P7,K4:K7,H7:I7,F5:J6,L4:O6,H4:J4F4=IF(COUNTIFS($H:$H,$L4,$I:$I,F$4)>0,"WORKING","FREE")
 
Upvote 0
Cferron said:
I reposting here again since the issue is a bit similar. I must ad a condition and I'm not sure how to deal with this :(
Basically if "ND" shows in column C for a specific employee on a specific date, I have to show "ND". Otherwise I go with the Countifs solution provided initially...

MrExcel.xlsx
ABCDEFGHIJKLMNOP
1No empl.DateCode hor.
20000441/19/22VACSH
30000441/20/22VACSHNo. empl1/19/221/20/221/21/221/22/221/23/221/24/221/25/221/26/221/27/221/28/221/29/22
40000441/20/22COV1544WORKINGNDWORKINGWORKINGFREEWORKINGWORKINGWORKINGFREEFREEWORKING
50000441/20/22ND559FREEWORKINGWORKINGFREEFREEWORKINGWORKINGFREEWORKINGWORKINGFREE
60000441/21/22VACST792FREEFREEFREEFREEFREEFREEFREEFREEWORKINGFREEFREE
70000441/22/22VACTR1222NDFREEWORKINGFREENDFREENDNDNDNDFREE
80000441/24/22VACST
90000441/25/22VACSTIf "ND" shows up in column C = Show ND
100000441/26/22VACST
110000441/29/22VACTRIf not present check if in columns A & B if countifs returns > 0
120005591/17/22J
130005591/18/22J
140005591/20/22J
150005591/21/22J
160005591/24/22J
170005591/25/22J
180005591/27/22J
190005591/28/22J
200007921/27/22FcS
210012221/19/22ND
220012221/21/22VACTR
230012221/23/22ND
240012221/25/22ND
250012221/26/22ND
260012221/27/22ND
270012221/28/22ND
Sheet1
Cell Formulas
RangeFormula
F4,P4:P7,K4:K7,H7:I7,F5:J6,L4:O6,H4:J4F4=IF(COUNTIFS($H:$H,$L4,$I:$I,F$4)>0,"WORKING","FREE")
Click to expand...
I was able to make it by determining if ND condition was met in the first place (simple IF), if not I would revert to the initial solution.
=IF(COUNTIFS($H:$H;$L8;$J:$J;"ND";$I:$I;O$4)>0;"ND";IF(COUNTIFS($H:$H;$L8;$I:$I;O$4)>0;"WORK";"FREE"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to vlookup the characters between a delimiter
Replies
3
Views
182
Fluff
Fluff
M
  • Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
298
MountainFog
M
B
  • Question
VLOOKUP with IF function
Replies
4
Views
110
Alex Blakenburg
A
Y
  • Solved
Variable decimal format: displayed value differs from the value used when referenced in another formula
Replies
4
Views
296
Peter_SSs
Peter_SSs
B
  • Question
How does one get around Xlookups duplicates vs filters size limitations?
Replies
5
Views
113
BEDE
B

Forum statistics

Threads
1,214,857
Messages
6,121,948
Members
449,056
Latest member
FreeCricketId

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