VLOOKUP match of column gives a result with a max of x

[MrO]

So what I am trying to do is use a pull down on one worksheet linked to a table on another worksheet allow the user to only input a number from 1 (always the minimum) to x (which is the number found in the table).
Currently it is showing #VALUE which is probably related to the pull down bringing up a term versus a number. I am unsure how to correct it.

=IF(B23="",1,MAX(VLOOKUP(B23,Talents!C4:Q134,4,FALSE)))


C23 is the cell i would like the user be able to select a number. The pull down is in B23 and it will bring up a term. that term is linked to a table on the Talents tab (in the C column) the maximum that x can be is found in column 4 in the table (the minimum is always 1 and the max can b anything from 1-10).

I am fairly new to excel so I am sure I am screwing something up. Thank you for any help.

[FDibbins]

If I understand you correctly, you want to simply return the value on TALENTS column E, based on whatever is entered in CHAR B22?

If that is the case, then you need to use...

=VLOOKUP(B22,Talents!$C$2:$E$35,3)

you can add the if(B22="", if you want to

[Hyperdude]

Hi MrO,

if you are trying to get the value for tier from the talents sheet. you can just use a vlookup in cell C22.

[MrO]

What I would like to end up with is based on what is in B23 (will link up with the left column in the table), I need C23 to only be able to return a number between 1 and x (x being the number in the 4th column of the table). Is that possible some way?
So the user can use the pull down in B23 to pick any one of the Talents and C23 will allow them to then pick the tier. That tier based on which Talent is selected can be anywhere from 1-X.
Does that make more sense?

[Hyperdude]

what you want is to retrieve the data keyed in by the user on char sheet to display on the talents sheet?

[MrO]

I would like it to show in C23 on the main tab if possible. If not some way for the user to only be able to key in a number between 1-x

[vlady]

maybe this could explain better or clear something on what you expect.

Test (7).xlsx

[MrO]

I tried using Data Validation but that is a fail also.
Using Whole Number choice a number between 1 and =VLOOKUP(B25,Talents!$C$4:$Q$134,4,FALSE)
It says the maximum evaluates to an error. Do you want to continue?

Tough to figure out

[vlady]

data validation

minimum
1

maximum
=d21

d21 is the vlookup formula provided by Fdibbins

[MrO]

I did a combination of the suggestions. Created another small table off to the side with the vlookup command as mentioned. Then used the data validation suggestion to pull from the table. It forces a user to select a tier number from 1 to whatever is in B23.
Thank you all for the help.