Hey all,
Can someone explain why in the nested formulas when she uses column, row, etc she uses 1:2 or A:B, even that the information she is trying to find, it is not in these rows, column?
For example:
=VLOOKUP($C$92,$C$86:$G$89,COLUMNS($A:B),0)
She has her information in the C to G columns, so why did she add as A:B)?
Thank you in advance
Regards,
Fernanda
Hi Fernanda,
The ROWS and COLUMNS functions return the number of columns in an array or reference. It's not about returning data relevant to the cells being looked up as such, it's simply returning a number as required in the col_index_number argument. Taking the example above, it evaluates to:
=VLOOKUP($C$92,$C$86:$G$89,2,0)
When this formula is copied across to the next column it becomes:
=VLOOKUP($C$92,$C$86:$G$89,COLUMNS($A:C),0)
Which evaluates to:
=VLOOKUP($C$92,$C$86:$G$89,3,0)
And so on. Usually the first row is referenced in ROWS - ROWS($1:1), and the first column is referenced in COLUMNS - COLUMNS($A:A) as this makes it clear that the count is starting at 1 and counting up from there.
I hope that clarifies things, but let me know if you have further questions.
Mynda
Hi Mynda,
That makes sense.
Thank you
Fernanda
