inserting value based on another cell

[monika_s]

Hi there

This is probably a dumb question but I have been asked to make it so that if you select one thing in a table it automatically puts a value in another cell. I have attempted to do this using the v-look up function but with no success. Attached is the spreadsheet with sample data in.

Column E is the Reason for the Award. There are 3 possible reasons (call quality, displaying core values and member of PSP winning team). Each of these gives a set value of points which I want to populate in column I. I have so far manually inputted them but this wants to be automated.

All help will be gratefully received.

Thanks

Monika

[ARGK]

Hi Monika
First of all may I say that no question is ever classed as "Dumb" as you put it. The purpose of this forum is to help one another, that's what the forum exists for. A question is only easy if you know the answer, even experts sometimes cannot see the wood for the trees and come to this forum for another point of view.
Now getting to your query, there is an error in your formula.
Your formula is: =VLOOKUP(E4,'v-look up table'!A1:B3,I4,)
and it should be: =VLOOKUP(E4,'v-look up table'!A1:B3,2)
This should be copied to cell I4 and copied down.
You have I4 to return a value from but it is outside the range you are searching and it should be a column number not a cell reference. The number "2" is the second column of the range you are searching and contains the value you want to return.
Also not all values have been created for all the entries in the "Reason For Award" column which is why you are getting "#N/A" appear when you copy the formula down the column. This can be hidden if you wish as follows:
Replace: =VLOOKUP(E4,'v-look up table'!A1:B3,2)
With: =IF(ISNA(VLOOKUP(E4,'v-look up table'!A1:B3,2)),"",VLOOKUP(E4,'v-look up table'!A1:B3,2))
This should be copied to cell I4 and copied down.
Hope this helps. If you need further help then please come back to us.
Good luck.
Tony

[monika_s]

Thank you so much Tony!

Amazing! I can't tell you how much head scratching I was doing this morning. There is nothing worse than falling out with Excel as it is usually my trusty friend. Your explanation is spot on.

Thanks again

Monika

[SDCh]

I see you use excel 2007, you can IFERROR Function, so the code can be like this (modify formula from ARGK):

Please Login or Register  to view this content.

[monika_s]

Thanks SDCh I shall give that a try.

Monika