Use Index Match or another lookup function?

[soilwork6]

Hello,

I would like to see what function you all would suggest to use for looking up a moving range of data based on date range of i.e rolling 30 days? Would it be an index match or vlookup etc.? For example I have a list of data in column cells that will be unique for that month but would have been used numerous times in prior than 30 days.
What I want the formula to do is when I type a unique identifier i.e XLTI4021 I want it to reference or "scan" a column next to it for todays date and the 30 days prior and highlight the cell in the column its looking up that way I know that the value I typed in (XLTI4021) has a match in the column next to it. There are also may be multiple data sets in each cell for the column its looking in. I.e the column being looked at may have up to 6 unique identifiers (XLTI4021, TVXA1245, XLTI8754, TVXA4589 etc..) Please let me know what you would use to perform this function. Thanks.

 

[etaf]

how would excel know that XLTI4021 also has 6 identifies XLTI4021, TVXA1245, XLTI8754, TVXA4589 etc..

you can use conditional formatting with a vlookup or index/match to find the identify in say column A and then highlight the row - OR highlight specific cells next to the value found

 

[soilwork6]

Thanks Wayne. For this example there could be one value in each cell in the column its referencing or there could be multiple values in the cell separated by a comma. If there was only one value in the cell I am assuming the formula could be a simple IF statement and then use a simple i.e TODAY - X days to look back 30 or so days in the column. But If I start having multiple values in the cell separated by a column I dont think excel can distinguish easily? Does this help clarify a bit better?

Here is a simple example:

For today's date I type in "dog" I want the formula to look at a reference column that the word "dog" is listed in Today - 30 days prior and conditional format and highlight both cells that "dog" is highlighted in. That way I know that "dog" was in listed in the reference column maybe 15 days ago or so, and that I dont have to go down my list to find that "dog" was typed in 15 days ago. It may be easier to look at multiple reference columns (up to 6 or 7) vs. having 6 or 7 values in one cell separated by a comma. I hope this clears it up a little bit. This may be easier than I thought... Thanks for your help.

how would excel know that XLTI4021 also has 6 identifies XLTI4021, TVXA1245, XLTI8754, TVXA4589 etc..

you can use conditional formatting with a vlookup or index/match to find the identify in say column A and then highlight the row - OR highlight specific cells next to the value found

 

[etaf]

not sure I still understand the data structure , so i may need a little more info
how each column is structured
anyway - you could lookup DOG using wildcards , if contained in a list in 1 cell
fred|bark|dog|cat
=VLOOKUP("*"&"dog"&"*",B8:C11,2,FALSE)
or a countifs() with wild cards and highlight values , using conditional formatting

perhaps you could load a sample onto onedrive, icloud or dropbox share - only one i would download from

 

[etaf]

or conditional formatting
=COUNTIF(B1:B20,"*"&"dog"&"*")=1
which will highlight DOG
or for a cell say A1
=COUNTIF($B1:$B20,"*"&$A$1&"*")=1
highlight the row

 

[soilwork6]

Thank you I appreciate it. When I type a value in a certain cell I want the formula to look in a column with rolling 30 days i.e todays date looking back 30 days. If the value that I type in matches what is in the reference range of Today through the 30 days prior, I want to highlight both values - the value I just typed in and the value where it was entered in the 30 day rolling range. The function of the formula is when I type something in a cell I want a formula to look at a reference range in another column including todays date, back 30 days. If what I type in a cell matches what is in a cell 30 days prior - highlight both values. I hope this clarifies and I will trying to upload an example sheet. Thanks!

 

[etaf]

ok , so my formula should work

how do we get back 30 days - is there a column with the dates in?
or is each column a date and so the range would be 30 columns for the last 30 days
i still dont know how the data is structured

we have a cell where you type DOG

then in other cells we have the word DOG on its own or part of a text |fred|dog|cat|

but where are the dates and rolling range ?

 

[soilwork6]

Yes there is a single column to the left with each date. Thanks Wayne

 

[etaf]

so i have used
column A = dates
column B = text to find
I2 - what to find

=AND(COUNTIF($B2,"*"&$I$2&"*"),$A2 > TODAY()-30)
as a conditional formatting for 30 days - red in example

and then for any date
=COUNTIF($B2,"*"&$I$2&"*")
green in example

https://www.dropbox.com/s/73z11d1w0nn5s46/lookup dates etaf.xlsx?dl=0

column D, E , F & G - show the different part of the formula

 

[soilwork6]

Wow, awesome! Thank you very much.