Combining Vlookups?

[atides33]

Hi everyone, new member here, though I've been lurking for awhile and have learned a lot! My question:

I have a spreadsheet that calculates Student grades based on a vlookup formula. I want the cell to do a second vlookup if a certain grade is calculated. For example, I have an assignment that is graded on the number of words written, but if a student gets an F, I want excel to calculate the grade based on growth from the previous assignment instead.

This is the sample data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Word Count 1[/TD]
[TD="align: center"]Word Count 2[/TD]
[TD="align: center"]Growth[/TD]
[TD="align: center"]Grade[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Joe Smith[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]146[/TD]
[TD="align: center"]=(B3/B2)-1[/TD]
[TD="align: center"]???[/TD]
[/TR]
</tbody>[/TABLE]


These are the sample vlookup tables for grades. if the grade is is calculated as an "F", I want excel to use the 2nd table instead.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Word Count Grades[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]143[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]165[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]177[/TD]
[TD]A+[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Growth Grades[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-100%[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]5%[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10%[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]15%[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]25%[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated. Thanks!

Chris

 

[AliGW]

Please provide your current VLOOKUP formula.

It'll be something along the lines of =IF(VLOOKUP, lookup value, lookup array)="F",VLOOKUP(lookup value, other lookup array),(VLOOKUP, lookup value, lookup array))

 

[coheed1785]

Chris, are you still looking for an answer for this?

 

[JohnGSnyder01]

This formula should work with $M$3:$N$10 representing the Growth Table and $P$3:$Q$10 representing the other Grade Table, C4=word count 2,d4=growth and cell e4 is the cell where this formual is entered

=IF(C4<95,VLOOKUP(D4,$M$3:$N$10,2,TRUE),VLOOKUP(C4,$P$3:$Q$10,2,TRUE))

 

[atides33]

Please provide your current VLOOKUP formula.

It'll be something along the lines of =IF(VLOOKUP, lookup value, lookup array)="F",VLOOKUP(lookup value, other lookup array),(VLOOKUP, lookup value, lookup array))

Sorry! I currently have the word count formula as follows: =VLOOKUP(B3,Words2,2) and the growth formula like this: =VLOOKUP(B46,Growth,2)

 

[atides33]

Yes.

 

[coheed1785]

Yes.

Can you see if this works?

=IF(VLOOKUP(B3,Words2,2,FALSE)<95,VLOOKUP(B46,Growth,2,FALSE),VLOOKUP(B3,Words2,2,FALSE))

 

[atides33]

This formula should work with $M$3:$N$10 representing the Growth Table and $P$3:$Q$10 representing the other Grade Table, C4=word count 2,d4=growth and cell e4 is the cell where this formual is entered

=IF(C4<95,VLOOKUP(D4,$M$3:$N$10,2,TRUE),VLOOKUP(C4,$P$3:$Q$10,2,TRUE))

This worked!! thanks so much, John!