vlookup with table_array constructed from mid formula
 

Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a range name of XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD

Where's the table?

This works

=VLOOKUP(MID(A1,4,2),C1:D14,2,0)




--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"JulieD" wrote in message
...
Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a range name of XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD

Hi!

Try this:

=VLOOKUP(5,INDIRECT(MID(A1,4,2)),2,0)

Biff

-----Original Message-----
Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a
range name of XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD


.

Hi Peo

the table is range that has been named XY (literally in cells A17:H25)

Cheers
JulieD

"Peo Sjoblom" wrote in message
...
Where's the table?

This works

=VLOOKUP(MID(A1,4,2),C1:D14,2,0)




--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"JulieD" wrote in message
...
Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a range name of
XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD

Doh!

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Biff" wrote in message
...
Hi!

Try this:

=VLOOKUP(5,INDIRECT(MID(A1,4,2)),2,0)

Biff

-----Original Message-----
Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a
range name of XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD


.

Hi Biff

now why didn't that work the first 3 times i tried it before posting!!!!
.... thanks, it works now

Cheers
JulieD


"Biff" wrote in message
...
Hi!

Try this:

=VLOOKUP(5,INDIRECT(MID(A1,4,2)),2,0)

Biff

-----Original Message-----
Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a
range name of XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD


.

Hi guys

figured out the reason why my formulas weren't working

any ideas if (and how) the following is possible with VLOOKUP (or another
function)
i have a workbook with column A containing State Names (e.g. WA,SA,NT)
column B through D is one freight company with different weight ranges
columns E through G is another freight company
column H through J is another

my idea was to use range names to create tables with column A and in the
first instance B through D (named XX)
then XY was a range consisting of column A and E through G
then YY was a range consisting of columns A and H through J
(all were rows 17 to 25)
so that i could do a vlookup on the state, in the appropriate table and
return the freight cost for a certain weighted item

(hope you're now not totally confused)
however VLOOKUP doesn't seem to like working with tables that aren't a
continual range - any ideas or do i need to restructure the tables?

(i then still need to use the INDIRECT(MID()) within this VLOOKUP)

Cheers
JulieD


"JulieD" wrote in message
...
Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a range name of XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD

You rename the parts of the table, XX = B:D, XY = E:G and YY = H:J then the
whole table for instance ABCD

=VLOOKUP(5,ABCD,2+VLOOKUP(MID(A1,4,2),{"XX",0;"XY" ,3;"YY",6},2,0),0)

so if you put 2 and use XX it gets the value from column B, but if it is XY
it returns the value from column E and YY from
column H

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"JulieD" wrote in message
...
Hi guys

figured out the reason why my formulas weren't working

any ideas if (and how) the following is possible with VLOOKUP (or another
function)
i have a workbook with column A containing State Names (e.g. WA,SA,NT)
column B through D is one freight company with different weight ranges
columns E through G is another freight company
column H through J is another

my idea was to use range names to create tables with column A and in the
first instance B through D (named XX)
then XY was a range consisting of column A and E through G
then YY was a range consisting of columns A and H through J
(all were rows 17 to 25)
so that i could do a vlookup on the state, in the appropriate table and
return the freight cost for a certain weighted item

(hope you're now not totally confused)
however VLOOKUP doesn't seem to like working with tables that aren't a
continual range - any ideas or do i need to restructure the tables?

(i then still need to use the INDIRECT(MID()) within this VLOOKUP)

Cheers
JulieD


"JulieD" wrote in message
...
Hi

i have a text string in cell A1 e.g.
ABCXYDEF
the XY bit will change.

What i want to do is lookup a value in a table that has a range name of
XY
e.g. =VLOOKUP(5,mid(A1,4,2),2,0)
but this doesn't seem to work.

how can i do this?

Cheers
JulieD