Understanding a formula

levanoj

Active Member
Joined
Oct 25, 2007
Messages
298
I have a formula that appears as follows in a cell:

=index(maplabel,B3)

When I use the "Evaluate Formula" tool in Excel to see the details of the formula I see the following:

=index(MAPPER!$C$1:$C$650,B3)

What I have figured out is that the value the formula returns is found in cell D1 in a different sheet of the workbook. I know this because when I type in a different value in cell D1 of that sheet, the change is reflected in the cell where the formula is programmed

Can anyone explain in lamens terms what the above formula is doing?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
1. maplabel is a named range. Found in Insert - Names - Define. It either has a direct reference to MAPPER!$C$1:$C$650, or a formula that returns that range..

2. The index formula is pretty cool. It takes these basic arguments..
=INDEX(Range,Row#,Column#).
column# is optional(if the range is only 1 column wide)

so it goes to that range, and returns the value in the corresponding Row# and Column #.

Example
=INDEX(A1:E10,4,3)
Would return the value in C4 (4th Row, 3rd Column)

so in your formula,
=index(maplabel,B3)

It first takes the named range maplabel and converts it to the actual range
MAPPER!$C$1:$C$650

so it's now
=index(MAPPER!$C$1:$C$650,B3)

Then it's taking the value of B3(a number say 10 for example) and returning the value from MAPPER!$C$1:$C$650 that is in the 10th Row (C10).

Hope that helps..
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,502
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top