Is there any way to to a vlookup in a table like this?
A B
1 10,000-24,999 1
2 25,000-59,999 2
3 60,000-149,999 3
4 150,000-500,000 4
..
..
10 75000

I want to look up the number in cell A10 in the list a1-a4 and return the b
column number. As you can see the problem is that a1-a4 is text - I was
trying to do something with the mid function starting at a search string
("-") with the whole thing in an array - but couldnt get anything to work.
Any ideas?
Thanks,
Yosef

WAAAYyyy too much work! :)

Try something like this:
Hdgs A B C
1 From Through MyValue
2 0 999 Unknown
3 1000 24999 1
4 25000 59999 2
5 60000 149999 3
6 150000 499999 4
7 500000 999999 Unknown

A10: 75000
A11: =VLOOKUP(A10,A2:C7,3,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"ynissel" wrote:

Is there any way to to a vlookup in a table like this?
A B
1 10,000-24,999 1
2 25,000-59,999 2
3 60,000-149,999 3
4 150,000-500,000 4
.
.
10 75000

I want to look up the number in cell A10 in the list a1-a4 and return the b
column number. As you can see the problem is that a1-a4 is text - I was
trying to do something with the mid function starting at a search string
("-") with the whole thing in an array - but couldnt get anything to work.
Any ideas?
Thanks,
Yosef

You won't get too far with a formula like this, but it works for the
criteria you've provided. However, this format would only last for up to 7
nested IFs.

=IF(VALUE(A10)<VALUE(LEFT(A2,FIND("-",A2)-1)),1,IF(VALUE(A10)<VALUE(LEFT(A3,FIND("-",A3)-1)),2,IF(VALUE(A10)<VALUE(LEFT(A4,FIND("-",A4)-1)),3,4)))


"ynissel" wrote in message
...
Is there any way to to a vlookup in a table like this?
A B
1 10,000-24,999 1
2 25,000-59,999 2
3 60,000-149,999 3
4 150,000-500,000 4
.
.
10 75000

I want to look up the number in cell A10 in the list a1-a4 and return the
b
column number. As you can see the problem is that a1-a4 is text - I was
trying to do something with the mid function starting at a search string
("-") with the whole thing in an array - but couldnt get anything to work.
Any ideas?
Thanks,
Yosef

cant break out the range into 2 columns as its a daily feed that I get - and
I wouldnt be able to automate it. Isnt there some array function I could use?

"Ron Coderre" wrote:

WAAAYyyy too much work! :)

Try something like this:
Hdgs A B C
1 From Through MyValue
2 0 999 Unknown
3 1000 24999 1
4 25000 59999 2
5 60000 149999 3
6 150000 499999 4
7 500000 999999 Unknown

A10: 75000
A11: =VLOOKUP(A10,A2:C7,3,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"ynissel" wrote:

Is there any way to to a vlookup in a table like this?
A B
1 10,000-24,999 1
2 25,000-59,999 2
3 60,000-149,999 3
4 150,000-500,000 4
.
.
10 75000

I want to look up the number in cell A10 in the list a1-a4 and return the b
column number. As you can see the problem is that a1-a4 is text - I was
trying to do something with the mid function starting at a search string
("-") with the whole thing in an array - but couldnt get anything to work.
Any ideas?
Thanks,
Yosef

Try this:

=INDEX(B1:B4,MATCH(A10,LEFT(A1:A4,FIND("-",A1:A4)-1)+0,1))

ctrl+shift+enter, not just enter


"ynissel" wrote:

Is there any way to to a vlookup in a table like this?
A B
1 10,000-24,999 1
2 25,000-59,999 2
3 60,000-149,999 3
4 150,000-500,000 4
.
.
10 75000

I want to look up the number in cell A10 in the list a1-a4 and return the b
column number. As you can see the problem is that a1-a4 is text - I was
trying to do something with the mid function starting at a search string
("-") with the whole thing in an array - but couldnt get anything to work.
Any ideas?
Thanks,
Yosef

Try this regular formula:

A11: =INDEX(B1:B4,MATCH(A10,INDEX(--LEFT(A1:A4,SEARCH("-",A1:A4)-1),0),1))

OR....this ARRAY FORMULA..(Committed with CTRL+SHIFT+ENTER, instead of
just ENTER)
A11: =INDEX(B1:B4,MATCH(A10,--LEFT(A1:A4,SEARCH("-",A1:A4)-1),1))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"ynissel" wrote:

cant break out the range into 2 columns as its a daily feed that I get - and
I wouldnt be able to automate it. Isnt there some array function I could use?

"Ron Coderre" wrote:

WAAAYyyy too much work! :)

Try something like this:
Hdgs A B C
1 From Through MyValue
2 0 999 Unknown
3 1000 24999 1
4 25000 59999 2
5 60000 149999 3
6 150000 499999 4
7 500000 999999 Unknown

A10: 75000
A11: =VLOOKUP(A10,A2:C7,3,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"ynissel" wrote:

Is there any way to to a vlookup in a table like this?
A B
1 10,000-24,999 1
2 25,000-59,999 2
3 60,000-149,999 3
4 150,000-500,000 4
.
.
10 75000

I want to look up the number in cell A10 in the list a1-a4 and return the b
column number. As you can see the problem is that a1-a4 is text - I was
trying to do something with the mid function starting at a search string
("-") with the whole thing in an array - but couldnt get anything to work.
Any ideas?
Thanks,
Yosef

Im having trouble getting it to work. Plus I realized that I oversimplified
it - as I have data in columns b and C so sometimes I need to offset 2 and
sometimes 3 from the search in column A. any thoughts?

"Teethless mama" wrote:

Try this:

=INDEX(B1:B4,MATCH(A10,LEFT(A1:A4,FIND("-",A1:A4)-1)+0,1))

ctrl+shift+enter, not just enter


"ynissel" wrote:

Is there any way to to a vlookup in a table like this?
A B
1 10,000-24,999 1
2 25,000-59,999 2
3 60,000-149,999 3
4 150,000-500,000 4
.
.
10 75000

I want to look up the number in cell A10 in the list a1-a4 and return the b
column number. As you can see the problem is that a1-a4 is text - I was
trying to do something with the mid function starting at a search string
("-") with the whole thing in an array - but couldnt get anything to work.
Any ideas?
Thanks,
Yosef

Or try this one :

=LOOKUP(E1;VALUE(SUBSTITUTE(LEFT($A$1:$A$4;FIND("-";$A$1:$A
$4)-1);",";""));$C$1:$C$4)

you get the text left to the "-", then you get rid of the "," then you
transform it into a value.....and then you look it up




The value to look for is in E1 and is a numeric value...

Regards

Carlos

For some reason I cant get it to work.

" wrote:

Or try this one :

=LOOKUP(E1;VALUE(SUBSTITUTE(LEFT($A$1:$A$4;FIND("-";$A$1:$A
$4)-1);",";""));$C$1:$C$4)

you get the text left to the "-", then you get rid of the "," then you
transform it into a value.....and then you look it up




The value to look for is in E1 and is a numeric value...

Regards

Carlos