Compare two columns from a table and get value from third column

Megaboost · #1 08-09-2019, 02:01 PM

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).

p45cal · #2 08-09-2019, 03:46 PM

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.)

Megaboost · #3 08-10-2019, 04:17 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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