Search, If & Input

mwiggins

I've been having some difficulty trying to solve this one:

• I have a master sheet where forms are inputted
• For each row there are two columns I need to set up in an IF for, and one where I need a value inputted from another sheet
• I imagine it like this: IF column 1@row = {other sheet} …This reference should search an entire column - this is where I am getting INCORRECT ARGUMENT. The point is that if column1@row is equal to the same value in any row fromt his other sheet in a specific column, Then
• IF [Created]@row is between two dates from the same {other sheet} ... I know how to do this one, Then
• INPUT the value in column in {other sheet}.

I'm not sure the combination of VLOOKUP / INDEX / IF(AND / etc functions I need to do.

 

The purpose behind this is really just a search query. If this name is also found in {other sheet} and the date is inbetween two dates from {other sheet} then take this third value from {other sheet} and input it.

Paul Newcome

Try using the JOIN function to pull the column from the other sheet into a text string with some sort of delimiter then using a FIND function to search for the name in that text string.

 

=IF(FIND([Column1]@row, JOIN({Name Column In Other Sheet}, ", ")) > 0, do this.............

mwiggins

The above formula works! I know I said I could do the date functions but as it turns out - I can't get them to work. I have a feeling it may be the order of operations but not sure if that matters in this case.

Paul Newcome

You would use an AND function.

 

=IF(AND(FIND(..............................) > 0, DATEONLY(Created@row) >= {First Date Reference}, DATEONLY(Created@row) <= {Second Date Reference}), then do this.....)

.

Although after having re-read your post, I believe an IF statement may not be the most efficient way to go. Try something like this...

 

=JOIN(COLLECT({Input Value Column}, {Name Column}, [Name Column]@row, {Date Column}, AND(@cell <= DATE(yyyy, mm, dd), @cell >= DATE(yyyy, mm, dd)))

 

Just replace the yyyy, mm, dd portions with the appropriate dates. If you have the start and end dates for the range already established in other cells within the sheet, you could replace the DATE functions altogether with cell references.