VLookup or If statment

[techgirl]

I have an excel book that if a particular group name is found, then place a number in the next cell. There are multiple groups names, multiple levels within the group, then the appropraite number within that level.


Example...
Column A could have group name A, B, C or D. If group A, then what level (From 1 - 8), then the appropriate number.

Example: If Group A, level 1, then enter the number 50. If group B, level 2, enter 45.

What is the best way to achieve the results??
Thanks, Techgirl

 

[Aladin Akyurek]

I have an excel book that if a particular group name is found, then place a number in the next cell. There are multiple groups names, multiple levels within the group, then the appropraite number within that level.


Example...
Column A could have group name A, B, C or D. If group A, then what level (From 1 - 8), then the appropriate number.

Example: If Group A, level 1, then enter the number 50. If group B, level 2, enter 45.

What is the best way to achieve the results??
Thanks, Techgirl

Set up a look up table like below on a separate sheet...

A2: A
B2: 1
C2: 50
A3: A
B3: 2
C3: 45
...

In D2 enter and copy down:

=A2&"|"&B2

Select the range in D and name it GL and the range in C VALUES.

Now you can invoke a simple look up formula...

=INDEX(VALUES,MATCH(A2&"|"&B2,GL,0))

where A2 houses a group and B2 a level.

=

 

[techgirl]

Here is the actual data, and it seems more complexed... the first half from Col A through E is the actually data typed in...Column F through I is where the answers will go.

So, I need to type in F1 the formula to match col A, once the name is found, then match the number in M1, placing the total value into F1 (Col F). So on through Col G, H, and I.

Name M1 M2 M3 Main M1 M2 M3 Main
CHAL 1 1 1 1 50 50 50 90
CRUISER 3 2 3 3 40 45 40 70
ROOKIE 1 1 1 1 40 40 40 80

 

[Aladin Akyurek]

Here is the actual data, and it seems more complexed... the first half from Col A through E is the actually data typed in...Column F through I is where the answers will go.

So, I need to type in F1 the formula to match col A, once the name is found, then match the number in M1, placing the total value into F1 (Col F). So on through Col G, H, and I.

Name M1 M2 M3 Main M1 M2 M3 Main
CHAL 1 1 1 1 50 50 50 90
CRUISER 3 2 3 3 40 45 40 70
ROOKIE 1 1 1 1 40 40 40 80

It's not clear what is matched against what in A1:I4... Care to clarify?

<TABLE style="WIDTH: 437pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=582><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2503" width=70><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=70>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>M1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>M2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>M3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Main</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>M1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>M2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>M3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Main</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>CHAL</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>90</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>CRUISER</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>70</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>ROOKIE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>80</TD></TR></TBODY></TABLE>

 

[techgirl]

This is for a game, and the first half of row one "Chal" has three tries (M1=1, M2=1, M3=1) and one Main Run=1; this is manually entered in. The second half of the same row is the part that will be searching the index table for the values. "Chal" is searched, and looks for M1=1, then returns 50 from the MT PTs ; second round "Chal" is searched for M2=1, then returns 50 from the MT PTs ; third round "Chal" is searched for M3=1, then returns 50 from the MT PTs. The final round "Chal" is searched for Main=1, then returns 90 from the "Main" index.

Data:
Name M1 M2 M3 Main M1 M2 M3 Main
CHAL 1 1 1 1 50 50 50 90
CRUISER 3 2 3 3 40 45 40 70
ROOKIE 1 1 1 1 40 40 40 80

Index Table:
Name Place MT Pts Main Pts
Chal 1 50 90
Chal 2 45 80
Chal 3 40 70
Chal 4 35 60
Chal 5 30 50
Chal 6 25 40
Chal 7 0 30
Chal 8 0 20
Cruiser 1 50 90
Cruiser 2 45 80
Cruiser 3 40 70
Cruiser 4 35 60
Cruiser 5 30 50
Cruiser 6 25 40
Cruiser 7 0 30
Cruiser 8 0 20
rookie 1 40 80
rookie 2 35 70
rookie 3 30 60
rookie 4 25 50
rookie 5 20 40
rookie 6 15 30
rookie 7 0 20
rookie 8 0 10

 

[techgirl]

I haven't heard back from you with an answer??

 

[Aladin Akyurek]

I haven't heard back from you with an answer??

Let A1:D25 house the index table, the headers included.

Let E1:M4 house the data as you call it.

J2, control+shift+enter, not just enter, copy across to L2, and down:

=INDEX($C$2:$C$25,MATCH(1,IF($A$2:$A$25=$E2,
     IF($B$2:$B$25=F2,1)),0))

M2, control+shift+enter, not just enter, and copy down:

=INDEX($D$2:$D$25,MATCH(1,IF($A$2:$A$25=$E2,
     IF($B$2:$B$25=I2,1)),0))

Hope I got the references right.

 

[techgirl]

I am receiving an #na error. What does the 1 and zero represent, I think the zero is for an exact match, but what is the 1 for??

 

[Aladin Akyurek]

I am receiving an #na error. What does the 1 and zero represent, I think the zero is for an exact match, but what is the 1 for??

Did you apply the suggested formulas as is or adjusted them to your workbook? Note that the formula requires control+shift+enter.

 

[techgirl]

I just pressed enter when I completed selecting my cell references, I haven't copied them down to the other cells yet.

A1:D25 is the entire database to be search upon (Index)
Column A = Names (Rookie, Chal, Expert)
Column B = Placed (1,2,3,4,5,6,7,8)
Column C = MT Points (50,40,30,20,10)
Column D = Main Points (I'm not worried about column D yet).


Data I want to compare and get the answer for:
H3 has Chal
I3 has 3
J3 - where the answer is going to go...

Search Column A and find Chal, then search column B find 3, Column C has the points of 40.
Cell J3 now has 40