Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-09-2019, 02:01 PM
Megaboost Megaboost is offline Compare two columns from a table and get value from third column Windows 10 Compare two columns from a table and get value from third column Office 2019
Novice
Compare two columns from a table and get value from third column
 
Join Date: Aug 2019
Posts: 2
Megaboost is on a distinguished road
Default Compare two columns from a table and get value from third column

I have a table of data with employee schedules for different events.


I am trying to represent the data in a weekly view separately to show for each day of the week which employees are scheduled for different events.


In the attached spreadsheet, in cell H6, I want to query [Shedule] table for events on the July 29 (H4) for employee Ron (F6).
Attached Files
File Type: xlsx events-by-date-by-employee.xlsx (12.8 KB, 9 views)
Reply With Quote
  #2  
Old 08-09-2019, 03:46 PM
p45cal's Avatar
p45cal p45cal is offline Compare two columns from a table and get value from third column Windows 10 Compare two columns from a table and get value from third column Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Array-enter (=commit the formula to the sheet with Ctrl+Shift+Enter, not just Enter) this formula in cell G5, then copy down and across:
Code:
=IFERROR(INDEX(Schedule[Event],MATCH(G$4&"|"&$F5,Schedule[Date]&"|"&Schedule[Employee],0)),"")
The above is in the attached.


Another way, not needing array-entering, is to enter the following in cell G5:
Code:
=IFERROR(INDEX(Schedule[Event],INDEX(MATCH(G$4&"|"&$F5,Schedule[Date]&"|"&Schedule[Employee],0),,)),"")
but then you can't copy across and down as before, but instead, select the whole range G5:M8 starting with G5 then press F2 on the keyboard (or just click in the formula) and then holding just the Ctrl key, press Enter.
(Yes, you can do this in one step by selecting G5:M8, put the formula in the formula bar, hold Ctrl and press Enter.)
Attached Files
File Type: xlsx msofficeforums43162events-by-date-by-employee.xlsx (13.4 KB, 6 views)
Reply With Quote
  #3  
Old 08-10-2019, 04:17 AM
Megaboost Megaboost is offline Compare two columns from a table and get value from third column Windows 10 Compare two columns from a table and get value from third column Office 2019
Novice
Compare two columns from a table and get value from third column
 
Join Date: Aug 2019
Posts: 2
Megaboost is on a distinguished road
Default

@p45Cal - Exactly what I was looking for. Thank you very much.
Reply With Quote
Reply

Tags
lookup formula, search column

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare two columns from a table and get value from third column Merge table cells across columns but skip if column don't exist jhearing Word Tables 4 05-31-2016 06:20 AM
Compare two columns from a table and get value from third column Column text flow with table across the columns ravenns Word 3 07-11-2013 11:29 PM
Compare two columns from a table and get value from third column Compare columns + calculate difference Inatic Excel 1 01-27-2013 08:00 AM
Compare two columns from a table and get value from third column Long, 3 Column Table - Can I make Fit Into Page Columns? Rigwald Word Tables 9 08-07-2012 08:14 PM
Compare two columns from a table and get value from third column How to compare 2 columns with other two columns in EXECL 2007? Learner7 Excel 5 06-12-2010 09:54 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft