Is it IF or vlookup or both?

[nigel_miguel]

Hello,

please beware with me, if my question is to simple... I'm working on developing my excel skills... but they are scarce...
i have a large worksheet with different data
it lists clients salary ranges (see below) I want to be able to match it the lower Bound range (also below).
I have the following data:
Range Lower Bound

under $30,000 0
$30,000 - $60,000 30,000
$60,001 - $90,000 60,001
$90,001 - $120,000 90,001
Over $120,000 120,000

How do i tell the cell when there is salary range 'under $30,000' i want to see 0, and so on for the others?

Thanks in advance.

[DP978]

I would just set up an if statement.

If(A1 < 30000, 0, If(A1 < 60000, 30000, And so on...

[ChemistB]

I'd create a small single column table of your lower boundries like so
0
30001
60001
90001
120001
and use VLOOKUP(B1,$A$1:$A$5,1) where B1 is the salary and A1:A5 is where that little table is. VLOOKUP will return the closest value without going over. Does that work for you?

[nigel_miguel]

it does not work, to the 1st response

[ChemistB]

See example attached

[nigel_miguel]

See example attached

That won't work because my original data is a range.
like the following:
under $30,000
$30,000 - $60,000
$60,001 - $90,000
$90,001 - $120,000
Over $120,000


in the column that has the possibility of any of these ranges, I would like to assign it a the lower value. For exmple: if it's under $30,000 i would like to see 0. If it's $30,000 - $60,000 i would like to see $30,000, and so on....

[ChemistB]

Yes, that formula works on a range basis. If you put any value between 0 and 30,000 into C2, D2 will return 0. If you put any value between 30,001 and 60,000, D2 will return 30,001 and so on. What do you need that is not covered by that?

[nigel_miguel]

Yes, that formula works on a range basis. If you put any value between 0 and 30,000 into C2, D2 will return 0. If you put any value between 30,001 and 60,000, D2 will return 30,001 and so on. What do you need that is not covered by that?

I don't think it works because the what is my cell is:
under $30,000 or $30,000 - $60,001 or the others... the cells contains the $ signs and the commas and dashes.

i did try your formula and it didn't work.

[NBVC]

Update the lookup column to:

0
30000
60001
90001
120001

You can format as Currency, if desired.

[nigel_miguel]

I got thanks!!!! how do I show this as resolved?

[NBVC]

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[ChemistB]

I guess I don't speak Canadian.

[NBVC]

I guess I don't speak Canadian.

LOL... i guess it's a subliminal understanding we've got...