Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
formula using both vlookup & hlookup | Excel Worksheet Functions | |||
Using Cell references in VLookUp | Excel Worksheet Functions |