@ in a indirect formula

[CY078]

Hi All

I'm looking at a formula and I know what it returns and where it came from ... but I don't quite understand why.

The formula is =@INDIRECT(VLOOKUP($O$2,Template!$A$3:$C$14,3,FALSE)&ROW())

I understand the green part (basically a lookup from a date to return the columns the data is in)

However I do not understand the significance of the
1. @ at the beginning of the formula
2. &ROW()) at the end of the formula (I understand that this will give the row ... but I don't understand how)

I've tried to lookup on the net .. which only gives me specifics of how indirects work.

Can anyone please shed some light on my issue ?

Thanks

Kind regards

Gary

 

[MARK858]

See either of the links below about the @

Implicit intersection operator: @ - Microsoft Support

Learn about the implicit intersection operator (@), which returns a single value using logic known as implicit intersection. The implicit intersection operator (@) may return a value, single cell range, or an error. The implicit intersection operator (@) is in a class of functions called dynamic...

support.microsoft.com

Implicit intersection: use of @ sign in Excel formulas

The tutorial covers Excel implicit intersection in full detail: what it is, how it behaves in Excel 365 and older version, what the @ symbol means in formulas and exactly how it works.

www.ablebits.com

Row() gives the row number that the formula resides in

 

[CY078]

See either of the links below about the @

Implicit intersection operator: @ - Microsoft Support

Learn about the implicit intersection operator (@), which returns a single value using logic known as implicit intersection. The implicit intersection operator (@) may return a value, single cell range, or an error. The implicit intersection operator (@) is in a class of functions called dynamic...

support.microsoft.com

Implicit intersection: use of @ sign in Excel formulas

The tutorial covers Excel implicit intersection in full detail: what it is, how it behaves in Excel 365 and older version, what the @ symbol means in formulas and exactly how it works.

www.ablebits.com

Row() gives the row number that the formula resides in

Ah I understand why i couldn't find it specifically. Cause I was searching for @indirect .. however the @ can be applied to any formula for implicit intersection

Then if the end of the formula is &row()) ... but there is nothing in between the brackets ... would that mean row 0 ?? I understand it if it has a cell reference ... but the bit I don't get is when there is nothing in between ???

 

[MARK858]

Ah I understand why i couldn't find it specifically. Cause I was searching for @indirect .. however the @ can be applied to any formula for implicit intersection

Then if the end of the formula is &row()) ... but there is nothing in between the brackets ... would that mean row 0 ?? I understand it if it has a cell reference ... but the bit I don't get is when there is nothing in between ???

Because it is referring to the current row the formula is in and not a separately numbered row or a cell reference.
Put

=ROW()

in a cell and you'll see.

 

[MARK858]

would that mean row 0 ??

You can't have row 0, it would error as it doesn't exist

 

[CY078]

@MARK858 ... yep I understand that now. It was referring to a cell (in the same row) but a gazillion columns to the right.
Thanks for your help.

TBH ... can't really say I like the formula. Makes more sense to "index" or "xlookup"

 

[MARK858]

You're welcome