# Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Function

1. ## Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Function

Hi,

I need to populate two columns based on a reference sheet like below.

For example the below is reference -

Position Ability Speed

1 High High

2 Medium High

3 Low High

4 Low Medium

5 Low Low

And now on another sheet of excel using the above reference I should be able to 'default' automatically using a Formula or Function the Ability & Speed values when there is Position value entered by User.

Example when - Position Ability Speed

1 High High

Request to share your suggestions. Thanks

Vk  Register To Reply

2. ## Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

Hello vk.excel. Welcome to the forum.

We really need an Excel sample workbook. Please see instructions in the 'gold' banner at the top of the page. HOW TO ATTACH YOUR SAMPLE WORKBOOK:  Register To Reply

3. ## Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

One way

=LOOKUP(\$F\$2,{1,2,3,4,5},{"Low","Low","Low","Medium","High"})

=LOOKUP(\$F\$2,{1,2,3,4,5},{"High","High","High","Medium","Low"})

OR set up table with data (say) columns A:C as per your posted file

then

=VLOOKUP(Position,\$A\$1:\$C\$5,2,0)

and

=VLOOKUP(Position,\$A\$1:\$C\$5,3,0)  Register To Reply

4. ## Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

Hi,

Thanks for the responses. Vlook up can be applied but still am looking for any logic that would populate upon entering 'Position' value.

Thanks,

Vk  Register To Reply

5. ## Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

With the formula is the desired cells they will populate when position is entered

=VLOOKUP(P1,\$A\$1:\$C\$5,2,0) position in P1

this in Q1

=VLOOKUP(P1,\$A\$1:\$C\$5,2,0) will return value in B

this in R1

=VLOOKUP(P1,\$A\$1:\$C\$5,3,0) will return value in C

so not sure what you want  Register To Reply

6. ## Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

Hi John,

I did vlook up and it is fetching result only for first few rows and later on it shows error - N/A - A Value is not available to the Formula or Function.

Though there are values for the Position in the reference, not sure why it is so?

For example - there is Ability value 'Low' for Position - 3, 4 & 5. Is it due to that and I dont think so.

Thanks  Register To Reply

7. ## Re: Need to populate two TEXT columns based on a Reference sheet. Looking for Formula/Func

Try

for first cell

=If (\$F2="","",LOOKUP(\$F2,{1,2,3,4,5},{,"High","Medium","Low","Low","Low"}))

my error!

and

=IF(\$f2="","",LOOKUP(\$F2,{1,2,3,4,5},{"High","High","High","Medium","Low"}))  Register To Reply