Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JulieD
 
Posts: n/a
Default 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


  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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




  #3   Report Post  
Biff
 
Posts: n/a
Default

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


.

  #4   Report Post  
JulieD
 
Posts: n/a
Default

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






  #5   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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


.





  #6   Report Post  
JulieD
 
Posts: n/a
Default

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


.



  #7   Report Post  
JulieD
 
Posts: n/a
Default

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




  #8   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
How do I use Range Names listed in a VLookup table in a formula? Essbasedvlpr32 Excel Worksheet Functions 3 December 15th 04 11:11 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM
formula using both vlookup & hlookup xFreeAdvice Excel Worksheet Functions 2 November 18th 04 12:19 AM
Using Cell references in VLookUp JonWilson631 Excel Worksheet Functions 1 November 4th 04 03:49 AM


All times are GMT +1. The time now is 02:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"