Using the result of a formula in another formula

[lele_lala]

Hi,

I have searched this forum and not found a solution to my problem yet, but everyone seems helpful here so I'm hopeful.

I have used a VLOOKUP to return a numerical value from text data from multiple columns. I have then averaged these results using a simple AVERAGE. I would now like to turn the numerical value results from the AVERAGE column, back in to the original text value using a VLOOKUP.

I have tried using a VLOOKUP within VLOOKUP but it won't work - I think because the lookup value is multiple cells with different values in. I've also tried using VALUE to transfer the result from AVERAGE in to a value, but it is returning the incorrect text value from the table. So for example, 4 should return 'proficient', but it is returning 'fail' which is a 0.

I hope someone can help me as I'm at a loss!

 

[gaz_chops]

Hi welcome to Mr Excel,

Explain what you mean by "return a numerical value from text data"

 

[lele_lala]

Hi welcome to Mr Excel,

Explain what you mean by "return a numerical value from text data"

Thanks for your reply!

So I am a teacher and I am collecting student assessment data on a scale of: Fail, Developing, Proficient, Advanced. I have used VLOOKUP to assign a number to each these, to allow me to create a numerical Average. I need to transfer this numerical average back to the original 'Fail, Developing' etc to give an average Grade.

 

[gaz_chops]

So you have a small table for your vlookup, as on the right below and want to acquire the text for the NEW ave value?

[TABLE="width: 609"]
<tbody>[TR]
[TD="width: 87"]Text[/TD]
[TD="class: xl65, width: 87, align: right"]Value[/TD]
[TD="class: xl65, width: 87, align: right"]average[/TD]
[TD="width: 87"]Text[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"]Table[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD]Fail[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]Developing[/TD]
[TD][/TD]
[TD="class: xl64"]Fail[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Advanced[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD]Proficient[/TD]
[TD][/TD]
[TD]Developing[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Proficient[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Advanced[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

If that is on right lines, then try something like

=INDEX($F$2:$F$5,MATCH(your vlookup formula that gives the ave value,$G$2:$G$5,0))

F2:G5 is the small table on the right (above).

 

[lele_lala]

Thank for this suggestion. It's not working for me at the moment! The average column is an AVERAGE formula of multiple VLOOKUP results. This is what the Table looks like:

Student Name Standard 1(text) Numerical Value Standard 2 Standard 3 Average Grade Joe Bloggs Developing 2 Advanced 4 Proficient 3 3 Proficient



So, I am trying to figure out how to get the red 'proficient' value at the end. I have used VLOOKUP to convert the text to the number for the standards above. The Average column is using the AVERAGE formula using the results of the VLOOKUP. There are multiple standards; up in to the 30s as it spans the whole academic year. I've just used 3 as an example here.

Thanks for you help so far, it's much appreciated!

 

[lele_lala]

Ah I copied the table and it didn't format correctly in my post. Hopefully this works.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student Name[/TD]
[TD]Standard 1 (text)[/TD]
[TD]Numerical Value[/TD]
[TD]S2[/TD]
[TD][/TD]
[TD]S3[/TD]
[TD][/TD]
[TD]Average[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]Joe Bloggs[/TD]
[TD]Proficient[/TD]
[TD]3[/TD]
[TD]Advanced[/TD]
[TD]4[/TD]
[TD]Developing [/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Proficient[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully this helps!

 

[mail2master]

The table that you have created for acquiring numerical numbers has 2 columns I guess ??
First column is comprised of text and second one is comprised of numerical value.

The most important thing to remember while using Vlookup formula is that "Lookup Value" should always be in the first column of the "Lookup array" (or should always be on the left side of column that you are trying to fetch value from), otherwise Vlookup would not yield any result.

In first case, your Vlookup is working fine because your Lookup Value is Text and that lies in the first column of "Lookup Table". But in second case, Vlookup won't work because, you are referring to same table and this time numerical value should be in first column of "Lookup table".

Your Data is in A1:B4

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Fail
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Developing
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Proficient
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Advanced
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]







Below Data is in A6:I7

[TABLE="class: grid, width: 1500, align: left"]
<tbody>[TR]
[TD="align: center"]Name
[/TD]
[TD="align: center"]S1
[/TD]
[TD="align: center"]Numeric
[/TD]
[TD="align: center"]S2
[/TD]
[TD="align: center"]Numeric
[/TD]
[TD="align: center"]S3
[/TD]
[TD="align: center"]Numeric
[/TD]
[TD="align: center"]Average
[/TD]
[TD="align: center"]Final Grade
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]Advance
[/TD]
[TD]VLOOKUP(B7,A1:B4,2,0)
[/TD]
[TD]Developing
[/TD]
[TD]VLOOKUP(D7,A1:B4,2,0)
[/TD]
[TD]Proficient
[/TD]
[TD]VLOOKUP(F7,A1:B4,2,0)
[/TD]
[TD]AVERAGE(C7,E7,G7)
[/TD]
[TD]=INDEX(A1:A4,MATCH(H7,B1:B4,0),1)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lele_lala]

Hi! Thanks for your reply! That table in A1:B4 is correct, however I have a second table which is reversed; so it has the numbers in the leftmost column, for use in the second VLOOKUP. So I'm referring to a different table, which means it should work?! I don't know.

 

[gaz_chops]

You don't need a 2nd table if you use the Index/Match formula as suggested. Unlike vlookup, Index/Match allows for the column for the lookup value to be in any order.