Hi guys

I have a table in excel 2007 and one of the columns is with dates

I need a formula to lookup the current day (today) in those columns and for every time it finds a match to get the text from a cell on that line.

For example if I have the table below

BMW 11.11.2018 Opel 11.11.2018 Audi 13.11.2018 Skoda 13.11.2018
Lets say the today date is 13.11.2018
I need the result in a cell to be

Audi


Skoda

Any ideas ?
Thanks

Here you go:

=IF(ISNA(INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4=TODAY( ),ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($B$1:$B1)))),"",INDEX($B$1:$B$4, SMALL(IF($A$1:$A$4=TODAY(),ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($B$1:$B1))))
Where Col B contains the list of cars
Col A the date/s

This is an array formula so confirm this with Ctrl+Shift+Enter

Thanks Marcia for your quick reply

I did what you told me

I pasted the formula in a cell and then I preses Ctrl+Shift+Enter and it gave me the message "the formula you typed contains an error"

Maybe like shown in the attached.

Attached Files

GetText.xlsx (10.6 KB, 7 views)

I pasted the formula in a cell and then I preses Ctrl+Shift+Enter and it gave me the message "the formula you typed contains an error"[/quote]
There is an error because "today" is Nov 14 and you do not have nov 14 in your data. Insert a cell where you type your reference date, like Xor's table. Replace TODAY() with the cell address of your reference date. Learn to name your table and range, it is very flexible when copying formulas down rows or across columns.
I guess IF ISNA is not applicable with INDEX MATCH function so remove / edit the formula. See the formula in D3 and E3, compare it with D2 and E2.

Attached Files

GetText.xlsx (15.9 KB, 9 views)

Xor, I don't think IFERROR works with Excel 2007. And maybe even INDEX MATCH?

Marcia, IFERROR was new in Excel 2007 and it works fine with both INDEX and MATCH.

If you don't want or can't use IFERROR then use =IF(ISERROR(....

I "sit down" corrected xor. Since the advent of MS Excel, the formulas I used was just the MDAS (multiplication, division, addition, subtraction) operations until two years ago when I started exploring and maximizing the many features of the program. I am wondering what's caused the formula i gave to Stai to return error messages when I had made sure that it was OK before posting it.

Marcia

You cannot use ISNA in the above case.
Select D4 and (in the Formula field) highlight exactly the following part of the formula:
=INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=TODAY( ),ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($B$2:$B4)))
press Ctrl+C to take a copy and paste it in an empty cell (say I4). Now put an equal sign in front of this and hold down Ctrl and Shift before pressing Enter to make it an array formula. With I4 selected press Evaluate Formula and press the Evaluate button 8 times after which you will see the following:
INDEX($B$2:$B$7, SMALL(IF(FALSE, FALSE, FALSE, FALSE, 5, 6), 3))
But the SMALL function can't return the third smallest value from an array with only 2 values (different from FALSE). That is why you get the #NUM! error.
And ISNA(#NUM!)=FALSE, so finally you have =IF(ISNA(#NUM!, "", #NUM!) which returns #NUM!

Thank you Xor for the precise instructions on how I should evaluate a formula. I didn't know about this process. I could now analyze how a formula with several nested functions work.

@Marcia
Don't forget the " evaluate formula tool"

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you Pecoflyer, that's a tool that I had never tried to explore before xor shared its function. Another ignorance ticked off, one more knowledge added.

You're welcome
and there is also this that might help
https://exceljet.net/tips/how-to-che...ormula-with-f9

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post