Google Sheets return value based on multiple conditions

Question

I have a google sheet that looks like this Empty sheet

In the same sheet, I have this source data: Source data

The name column is the exact same names that are arranged horizontally in the sheet The status column is a text

My goal is to add either an "X" or "O" in the cells under each name and next to the appropriate date column based on the status of each person on a specific day.

If status = "On site" then put "X", if status is "Off site" then put "O"

The output should look something like this: Goal

So 3 things should match: name, status, and date

I tried using IF() functions with AND() but it's not giving me the desired output.

I tried the following:

=IF(AND(T15="Person 1", U15="On site", V15=A2), "X", "O")

V15 is the cell containing the source date and A2 is the cell containing the destination date

Why is this not working? What am I doing wrong? any help is greatly appreciated!

@player0 Should I export as an excel file or just link to the google sheet? — SevenSouls, Dec 10 '21 at 22:50
@player0 https://docs.google.com/spreadsheets/d/e/2PACX-1vQKuilT31ylHWT6hRxJVqcy0pDrAkOSfFFQ8H4HQPx3zxzPPgiaqBr2gW5-Z_0ywPBxcI__cGKdm3nV/pubhtml — SevenSouls, Dec 10 '21 at 22:52
@player0 https://docs.google.com/spreadsheets/d/1kNAhu3LNkawxCRZJRGlGkOZVlpNyRz-9ZQB7ePQoc-E/edit?usp=sharing — SevenSouls, Dec 10 '21 at 22:53
@player0 In the same sheet on the right side. You may need to scroll a bit — SevenSouls, Dec 10 '21 at 22:56
@player0 The actual data is coming from a google form. I just put fictitious data here for the sake of this post. The format is same — SevenSouls, Dec 10 '21 at 22:57
About sharing spreadsheets, do note that [it exposes your email address](https://meta.stackoverflow.com/questions/394304/). So, use a dummy Google account if you consider your email address to be private. — TheMaster, Dec 11 '21 at 23:48
@TheMaster Thank you for the reminder. I will consider that from now on. — SevenSouls, Dec 12 '21 at 00:09
While external links are welcome, consider adding a [table](https://meta.stackexchange.com/questions/356997/) or screenshots or csv text(like [this](https://stackoverflow.com/a/64186520/) by *export*ing as csv) to show your data structure in the future. — TheMaster, Dec 12 '21 at 00:10

player0 · Accepted Answer · 2021-12-12T00:26:51.640

0

try in C2:

=INDEX(IFNA(SUBSTITUTE(SUBSTITUTE(VLOOKUP(A2:A, 
 QUERY({T15:V22; SPLIT(FLATTEN(C1:M1)&"×!×1", "×")}, 
 "select Col3,max(Col2) where Col2 is not null group by Col3 pivot Col1"), 
 MATCH(C1:M1, TRANSPOSE(SORT(FLATTEN(C1:M1))), 0)+1, 0), 
 "On site", "X"), "Off site", "O")))

update

optimized formula:

=INDEX(IFNA(IF(A2:A="",,SUBSTITUTE(SUBSTITUTE(VLOOKUP(TO_TEXT(A2:A),QUERY({QUERY(TEXT(
 IMPORTRANGE("1IANXcVXKRMj591vs_jLkHAQp-ZCoZFpbf7xOSm2srJA", "Form Responses 1!A2:C"), 
 {"m/d/yyyy", "@", "@"}), "where Col2 <> '0'");
 SPLIT("!×"&FLATTEN(C1:M1)&"×!", "×")},
 "select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"),
 MATCH(C1:M1, TRANSPOSE(SORT(FLATTEN(C1:M1))), )+1, ),
 "Working alone", "X"), "Not working alone/Not working", "O"))))

edited Dec 12 '21 at 00:26

answered Dec 10 '21 at 23:17

player0

124,011
12
67
124

Wow thank you!! Any chance you could tell me logic behind these nested functions? I will look up each function to find out more but how are you using them together here to achieve this? Thanks!! appreciate your response! – SevenSouls Dec 11 '21 at 01:38
@badi sure, the idea is simple.. we take the range T15:V22 and extend it with all possible names C1:M1 because T15:T22 does not or may not contain all names which we need for pivoting. so we create an array {} and pass it to QUERY and just pivot all names placing dates as first column se we would be able to do VLOOKUP based on dates. in VLOOKUP we select range A2:A and return exact matches from our QUERY. but because names are not or may not be in same order in T15:T22 and C1:M1 we run simple MATCH to return a sequence of numbers where number = column number. – player0 Dec 11 '21 at 02:00
but our first column of QUERY are dates and we want to return stuff after dates so we make a small correction +1 and as last thing we just substitute stuff for custom strings – player0 Dec 11 '21 at 02:00
Thank you! In the data argument in the QUERY function what is the SPLIT function used for? and after the & symbol what are the character inside ""? – SevenSouls Dec 11 '21 at 03:39
@badi as said... we take names from C1:M1 and append unique symbol × (you can change it if you wish for any symbol ♥♦♣♠♂♀♫♪☼►) to every name then we append ! (you can change it for any text you wish) again unique symbol × and 1 (again any number smaller than 40000 - representing the date value). next, we FLATTEN it into one column and SPLIT it into 3 columns (to match our previous 3-columned range T15:V22) cropping it on those unique symbols – player0 Dec 11 '21 at 03:48
@badi answer updated – player0 Dec 12 '21 at 00:27

Google Sheets return value based on multiple conditions

1 Answers1

update