# I have a named range on google sheet and I want to access the informations in it from a different sheet with if condition

Let's say I have 2 sheets. One with character stats and one with the tables for the character data.

One sheet is called "Character" and it has 4 columns of data: Level, Class, HP and Mana.

I J K L
1  Level Class HP Mana
2 1 Warrior (depending on the level, it select the appropriate row) (depending on the level, it select the appropriate row)
3 2 Rogue (depending on the level, it select the appropriate row) (depending on the level, it select the appropriate row)
4 8 Mage (depending on the level, it select the appropriate row) (depending on the level, it select the appropriate row)

The Class is manually specified, but then I want to access the data from another sheet, depending on the chosen Class.

The other sheet is called "Stats". The data is laid out as follows:

A B C
1
2 Level HP Mana
3 20 40
4 30 50
5 etc etc etc

In Stats I have created named ranges for HP and Mana.

I want to use Level from Character to return the correct HP or Mana value that is listed for that level in Stats

Here's the formula I used:

=INDEX(HP; MATCH(I2; Stats!\$A\$3:\$A\$12; 1))

So I compare the index from the named range HP with the data from my cell I2. If they are equivalent, use the data on the specified row from the sheet Stats.

Formula syntax matches locale with comma as radix point
eg. 1 000,00 or 1.000,00 instead of 1 000.00 or 1,000.00
NA/UK syntax follows at the end of the answer.

# VLOOKUP

VLOOKUP will locate the row and return the value with a single function. This is a simpler approach than combining INDEX and MATCH.

## Basic Formula

=VLOOKUP(I2; Stats!A3:C; 2)   //  HP
=VLOOKUP(I2; Stats!A3:C; 3)   //  Mana

All formulas assume Stats is sorted ascending by Column A.
If not, add argument is_sorted=FALSE to them
eg. =VLOOKUP(I2;Stats!A3:C;2;FALSE)
or  =VLOOKUP(I2;Stats!A3:C;2;0)
eg. =VLOOKUP(I2;Stats!A3:C;2;FALSE)
or  =VLOOKUP(I2;Stats!A3:C;2;0)

Important Note: The formulas on this page assume Stats is sorted by Column A ascending. If not, add argument is_sorted=FALSE to them. For example:

Use this:   =VLOOKUP(I2; Stats!A3:C; 2; FALSE)
Equivalent: =VLOOKUP(I2; Stats!A3:C; 2; 0)

## Array Version

You can calculate the results for all rows with one formula. Put the formula in row 1 of Character. The formula includes the heading text and is placed above the data in row 1 so that it's not affected by sorting and modifying your data rows:

HP formula in K1
={"HP"; INDEX(IF(LEN(I2:I); VLOOKUP(I2:I; Stats!A3:C; 2))}

Mana formula in L1
={"Mana"; INDEX(IF(LEN(I2:I); VLOOKUP(I2:I; Stats!A3:C; 3))}

Combined HP & Mana formula in K1
={"HP"\"Mana"; INDEX(IF(LEN(I2:I); VLOOKUP(I2:I; Stats!A3:C; {2\3}))}

### NA/UK Style Syntax

# Basic Formulas
=VLOOKUP(I2, Stats!A3:C, 2)         //  HP
=VLOOKUP(I2, Stats!A3:C, 3)         //  Mana
=VLOOKUP(I2, Stats!A3:C, 2, FALSE)  //  Stats not sorted by A Asc.
=VLOOKUP(I2, Stats!A3:C, 2, 0)      //  Equivalent

# Array Formulas
={"HP", INDEX(IF(LEN(I2:I), VLOOKUP(I2:I, Stats!A3:C, 2))}
={"Mana", INDEX(IF(LEN(I2:I), VLOOKUP(I2:I, Stats!A3:C, 3))}
={"HP","Mana"; INDEX(IF(LEN(I2:I), VLOOKUP(I2:I, Stats!A3:C, {2,3}))}