# 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

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:

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)

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

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

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

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"}))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1