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!

 

[lele_lala]

I was so sure this was going to work...alas, the #N/A is back to haunt me! Could it be that because the cell listed as H7 above is a formula, rather than a value?! I am getting towards the end of my patience with it!

I'm not too sure on the term 'array' in context of VLOOKUP, so that could be contributing?

 

[gaz_chops]

Post the formula that you are trying.

 

[lele_lala]

=index(a25:a31,match(v3,b25:b31,0),1)

 

[gaz_chops]

OK, in an empty cell enter

=V3=B??

Change ?? to be the cell with the number that matches.

What result do you get?

 

[mail2master]

@gaz_chops is right... You don't need second table if you follow the same formula as I suggested.

 

[lele_lala]

FALSE

Sorry, switched the thing off for the weekend!

 

[gaz_chops]

Then they do not match, which is why the vlookup doesn't find it.

It suggests that one of the values is text whilst the other is a number.
If one is the result of a formula, does the formula tell it to return e.g. "2" using quotation marks either side? If so, then this is returning text NOT a number, remove the quotation marks from the numbers.