Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Help With Vlookup

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello Everyone,
I need to get excel to deliver a sum from 75 different arrays that are spreed out over 15 separate worksheets (in the same workbook ).

My problem is this, Each array will have a slightly different set of values in the far left column; which returns the error "A value is not available to the formula or function".

In Plain English, If BOB, TOM and SUE are in the left column in the first array, and BOB, BILL and Sue are in the first column of the second array; then the error occurs when I search for TOM because he is not in the second array.

This is the formula i am using.


Please help! Im so Stuck, I need a workaround!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics

I have a nested series of about 7 different lookup tables that work like this:


I changed the actual lookups to "# ARRAY" for simplicity's sake. The formula itself does what I need it to do, but I am wondering if there is a way for conditional formatting to indicate which array the result is being taken from. For example, if the result is being taken from the 1st array, color it blue. If it's being taken from the 2nd array, color it red, 3rd array, green, and so on and so forth. Can this be done?

I want to create a vlookup function that uses as the the 1st argument the =LEFT() function.

=vlookup(left(A1,3),Array1,10, false)

The problem is 'how do I manipulate the Array argument so that it too catches the 1st 3 characters in the array?

For example, the array:

Yankees 10 100
Red Sox 20 300
Yankees 11 350
Orioles 22 500

Yankees, NY
So, the vlookup would find the "Yan" in "Yankees, NY" above, then go to the array & return, let's say '10'.

Also, how would the vlookup look if I wanted to return 21 (i.e., 10 + 11), basically ALL of the instances of "Yan" in the array. Maybe some kind of SUMIF combined with a vlookup? Please help. Thanks.


I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.

I've tried the following but got an error stating it was too long:

= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))

The repeating line needs attention in my mind:

MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))


I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.

I've tried the following but got an error stating it was too long:

= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))

The repeating line needs attention in my mind:

MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))


I have a workbook that if I use just the array formula for the totals it works fine and if I use data validation and vlookup and the array formula for the totals it is giving me a #Value! instead.
I have tried a couple of things and did find that as long as I do not use the vlookup in the one column (column d) the array formula at the bottom works but as soon as I add the vlookup to column D I got the error.

I want to use the vlookup to get the values for items on a different page - along with the data validation and then use the array formula to get a total for multiple items in the above columns.

I hope this makes sense. I have attached a workbook to show what I am talking about.

I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.

Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:


                      A                       B                      C                      D
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)

To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns B-D, but doing that gives me the result below:

                      A                       B                      C                      D
                      A                       B                      C                      D
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)

As you can see, I still have to go in to Columns B-D and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?

Vlookup() will search for a specified value in the first column of an array and return the value on the same row in a specified numbers of columns to the right of the first column in the array. Vlookup is a great function, however, it is often the case I would like to reverse the way vlookup() works. I would like to have it search the specified value in the last row and return the value of cell left of the last column.

Up to now I have been rigging some work arounds but I would like to find a cleaner solution.

here is the scenario:

A Column (array of values)

B Colum (2nd array of values)

D1 Cell = Vlookup formula looking B values into A colum array.

Problem: When repeating the formula down D column it increases automatically the cells row# within the formula. This is fine to take next B row value to compare but array of values in column A remains the same.

sample on D1: =VLOOKUP(B1,A1:A200,1)
sample on D2: =VLOOKUP(B2,A2:A201,1) <--- problem !!
sample on D2: =VLOOKUP(B3,A3:A202,1) <--- Problem !!

Problem: I want array to stay A1:A200


I recorded the following macro opening a .csv file
(then I modified the filename)

The number of rows in the .csv file changes every day.
It seems to work properly everytime I run it but I don't understand the FieldInfo Arrays.
Consequently my concern is will the code not function as desired some day if the .csv file gets too long?


    Workbooks.OpenText Filename:=myPath & "\MySpreadsheet.xls", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
        28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _
        Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array( _
        41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), _
        Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array( _
        54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _
        Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array( _
        67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1), _
        Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array( _
        80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), _
        Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array( _
        93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1), _
        Array(100, 1), Array(101, 1), Array(102, 1), Array(103, 1), Array(104, 1), Array(105, 1), _
        Array(106, 1), Array(107, 1), Array(108, 1), Array(109, 1), Array(110, 1), Array(111, 1), _
        Array(112, 1), Array(113, 1), Array(114, 1), Array(115, 1), Array(116, 1), Array(117, 1), _
        Array(118, 1))

Below you'll see the long sheet. I highlighted the cells with issues. Seems my VLOOKUP is not pulling correct info. Help please.
Excel 2003 I J K L M N O P 2 TRIP NUMBER IN courier 3 GUA UPS Counts Trips Name 4 S27 UPS FedEx 0 10 United 5 113 NOW UPS 0 15 United 6 3 NOW NOW 0 20 United 7 134 NOW US MAIL 0 21 United 8 113 NOW Prestige 0 23 United 9 316 NOW #N/A 0 24 United 10 323 NOW 25 United 11 121 NOW 30 United 12 213 NOW 33 United 13 25 NOW 34 United 14 102 NOW 35 United 15 PAY #N/A 36 United 16 528 NOW 101 NOW 17 109 NOW 102 NOW 18 UAM UPS 103 NOW 19 47 NOW 104 NOW 20 AKR Premier 105 NOW 21 UAM UPS 106 NOW 22 UAM UPS 107 NOW 23 S27 UPS 108 NOW 24 30A NOW 109 NOW 25 25A NOW 110 NOW 26 AKR Premier 111 NOW 27 48 NOW 112 NOW 28 E26 USCARGO 113 NOW 29 FPM FedEx 114 NOW 30 CLE Premier 115 NOW 31 20 NOW 12 USMAIL 32 30A NOW 120 NOW 33 15 NOW 121 NOW 34 25A NOW 122 NOW 35 CLE Premier 123 NOW 36 34 NOW 124 NOW 37 44 NOW 125 NOW 38 33 NOW 126 NOW 39 24 NOW 127 NOW 40 CLE Premier 130 NOW 41 24 NOW 131 NOW 42 33 NOW 134 NOW 43 CLE Premier 135 NOW 44 FAM FedEx 136 NOW 45 FAM FedEx 137 NOW 46 UAM UPS 138 NOW 47 30A NOW 139 NOW 48 S27 UPS 140 NOW 49 FAM FedEx 143 NOW 50 CLE Premier 144 NOW 51 S27 UPS 145 NOW 52 FAM FedEx 15A United 53 323 NOW 202 NOW 54 COL Premier 203 NOW 55 CLE Premier 204 NOW 56 CLE Premier 205 NOW 57 GUA UPS 206 NOW 58 106 NOW 207 NOW 59 MUP #N/A 208 NOW 60 CLE Premier 209 NOW 61 PAY #N/A 210 NOW 62 CLE Premier 211 NOW 63 CLE Premier 212 NOW 64 AKR Premier 213 NOW 65 214 NOW 214 NOW 66 PAY #N/A 21A United 67 PAY #N/A 25A United 68 FAM FedEx 26 USCARGO 69 GUA UPS 301 NOW 70 CLE Premier 302 NOW 71 CLE Premier 303 NOW 72 FAM FedEx 304 NOW 73 23 NOW 305 NOW 74 CLE Premier 306 NOW 75 COL Premier 307 NOW 76 S27 UPS 309 NOW 77 CLE Premier 30A United 78 COL Premier 310 NOW Oct2010

Worksheet Formulas Cell Formula J3 =VLOOKUP( LEFT( I3 )&"*",$O$3:$P$104,2,FALSE ) J4 =VLOOKUP( LEFT( I4 )&"*",$O$3:$P$104,2,FALSE ) J5 =VLOOKUP( LEFT( I5 )&"*",$O$3:$P$104,2,FALSE ) J6 =VLOOKUP( LEFT( I6 )&"*",$O$3:$P$104,2,FALSE ) J7 =VLOOKUP( LEFT( I7 )&"*",$O$3:$P$104,2,FALSE ) J8 =VLOOKUP( LEFT( I8 )&"*",$O$3:$P$104,2,FALSE ) J9 =VLOOKUP( LEFT( I9 )&"*",$O$3:$P$104,2,FALSE ) J10 =VLOOKUP( LEFT( I10 )&"*",$O$3:$P$104,2,FALSE ) J11 =VLOOKUP( LEFT( I11 )&"*",$O$3:$P$104,2,FALSE ) J12 =VLOOKUP( LEFT( I12 )&"*",$O$3:$P$104,2,FALSE ) J13 =VLOOKUP( LEFT( I13 )&"*",$O$3:$P$104,2,FALSE ) J14 =VLOOKUP( LEFT( I14 )&"*",$O$3:$P$104,2,FALSE ) J15 =VLOOKUP( LEFT( I15 )&"*",$O$3:$P$104,2,FALSE ) J16 =VLOOKUP( LEFT( I16 )&"*",$O$3:$P$104,2,FALSE ) J17 =VLOOKUP( LEFT( I17 )&"*",$O$3:$P$104,2,FALSE ) J18 =VLOOKUP( LEFT( I18 )&"*",$O$3:$P$104,2,FALSE ) J19 =VLOOKUP( LEFT( I19 )&"*",$O$3:$P$104,2,FALSE ) J20 =VLOOKUP( LEFT( I20 )&"*",$O$3:$P$104,2,FALSE ) J21 =VLOOKUP( LEFT( I21 )&"*",$O$3:$P$104,2,FALSE ) J22 =VLOOKUP( LEFT( I22 )&"*",$O$3:$P$104,2,FALSE ) J23 =VLOOKUP( LEFT( I23 )&"*",$O$3:$P$104,2,FALSE ) J24 =VLOOKUP( LEFT( I24 )&"*",$O$3:$P$104,2,FALSE ) J25 =VLOOKUP( LEFT( I25 )&"*",$O$3:$P$104,2,FALSE ) J26 =VLOOKUP( LEFT( I26 )&"*",$O$3:$P$104,2,FALSE ) J27 =VLOOKUP( LEFT( I27 )&"*",$O$3:$P$104,2,FALSE ) J28 =VLOOKUP( LEFT( I28 )&"*",$O$3:$P$104,2,FALSE ) J29 =VLOOKUP( LEFT( I29 )&"*",$O$3:$P$104,2,FALSE ) J30 =VLOOKUP( LEFT( I30 )&"*",$O$3:$P$104,2,FALSE ) J31 =VLOOKUP( LEFT( I31 )&"*",$O$3:$P$104,2,FALSE ) J32 =VLOOKUP( LEFT( I32 )&"*",$O$3:$P$104,2,FALSE ) J33 =VLOOKUP( LEFT( I33 )&"*",$O$3:$P$104,2,FALSE ) J34 =VLOOKUP( LEFT( I34 )&"*",$O$3:$P$104,2,FALSE ) J35 =VLOOKUP( LEFT( I35 )&"*",$O$3:$P$104,2,FALSE ) J36 =VLOOKUP( LEFT( I36 )&"*",$O$3:$P$104,2,FALSE ) J37 =VLOOKUP( LEFT( I37 )&"*",$O$3:$P$104,2,FALSE ) J38 =VLOOKUP( LEFT( I38 )&"*",$O$3:$P$104,2,FALSE ) J39 =VLOOKUP( LEFT( I39 )&"*",$O$3:$P$104,2,FALSE ) J40 =VLOOKUP( LEFT( I40 )&"*",$O$3:$P$104,2,FALSE ) J41 =VLOOKUP( LEFT( I41 )&"*",$O$3:$P$104,2,FALSE ) J42 =VLOOKUP( LEFT( I42 )&"*",$O$3:$P$104,2,FALSE ) J43 =VLOOKUP( LEFT( I43 )&"*",$O$3:$P$104,2,FALSE ) J44 =VLOOKUP( LEFT( I44 )&"*",$O$3:$P$104,2,FALSE ) J45 =VLOOKUP( LEFT( I45 )&"*",$O$3:$P$104,2,FALSE ) J46 =VLOOKUP( LEFT( I46 )&"*",$O$3:$P$104,2,FALSE ) J47 =VLOOKUP( LEFT( I47 )&"*",$O$3:$P$104,2,FALSE ) J48 =VLOOKUP( LEFT( I48 )&"*",$O$3:$P$104,2,FALSE ) J49 =VLOOKUP( LEFT( I49 )&"*",$O$3:$P$104,2,FALSE ) J50 =VLOOKUP( LEFT( I50 )&"*",$O$3:$P$104,2,FALSE ) J51 =VLOOKUP( LEFT( I51 )&"*",$O$3:$P$104,2,FALSE ) J52 =VLOOKUP( LEFT( I52 )&"*",$O$3:$P$104,2,FALSE ) J53 =VLOOKUP( LEFT( I53 )&"*",$O$3:$P$104,2,FALSE ) J54 =VLOOKUP( LEFT( I54 )&"*",$O$3:$P$104,2,FALSE ) J55 =VLOOKUP( LEFT( I55 )&"*",$O$3:$P$104,2,FALSE ) J56 =VLOOKUP( LEFT( I56 )&"*",$O$3:$P$104,2,FALSE ) J57 =VLOOKUP( LEFT( I57 )&"*",$O$3:$P$104,2,FALSE ) J58 =VLOOKUP( LEFT( I58 )&"*",$O$3:$P$104,2,FALSE ) J59 =VLOOKUP( LEFT( I59 )&"*",$O$3:$P$104,2,FALSE ) J60 =VLOOKUP( LEFT( I60 )&"*",$O$3:$P$104,2,FALSE ) J61 =VLOOKUP( LEFT( I61 )&"*",$O$3:$P$104,2,FALSE ) J62 =VLOOKUP( LEFT( I62 )&"*",$O$3:$P$104,2,FALSE ) J63 =VLOOKUP( LEFT( I63 )&"*",$O$3:$P$104,2,FALSE ) J64 =VLOOKUP( LEFT( I64 )&"*",$O$3:$P$104,2,FALSE ) J65 =VLOOKUP( LEFT( I65 )&"*",$O$3:$P$104,2,FALSE ) J66 =VLOOKUP( LEFT( I66 )&"*",$O$3:$P$104,2,FALSE ) J67 =VLOOKUP( LEFT( I67 )&"*",$O$3:$P$104,2,FALSE ) J68 =VLOOKUP( LEFT( I68 )&"*",$O$3:$P$104,2,FALSE ) J69 =VLOOKUP( LEFT( I69 )&"*",$O$3:$P$104,2,FALSE ) J70 =VLOOKUP( LEFT( I70 )&"*",$O$3:$P$104,2,FALSE ) J71 =VLOOKUP( LEFT( I71 )&"*",$O$3:$P$104,2,FALSE ) J72 =VLOOKUP( LEFT( I72 )&"*",$O$3:$P$104,2,FALSE ) J73 =VLOOKUP( LEFT( I73 )&"*",$O$3:$P$104,2,FALSE ) J74 =VLOOKUP( LEFT( I74 )&"*",$O$3:$P$104,2,FALSE ) J75 =VLOOKUP( LEFT( I75 )&"*",$O$3:$P$104,2,FALSE ) J76 =VLOOKUP( LEFT( I76 )&"*",$O$3:$P$104,2,FALSE ) J77 =VLOOKUP( LEFT( I77 )&"*",$O$3:$P$104,2,FALSE ) J78 =VLOOKUP( LEFT( I78 )&"*",$O$3:$P$104,2,FALSE )

The following placed in cell A1 results in "hello":

However, I want to remove the array from the VLOOKUP and put it in a separate cell because I will have multiple VLOOKUPs on the same array data.

So I tried putting the 2x2 array in cell B1:

And in cell A1, I put the formula:

But then A1 returns a #REF! error. How can I get this to work?

I searched for an answer, and the following looks similar to what I am trying to accomplish (using INDIRECT), but I can't get it to work.

Any help would be much appreciated, thanks!


I've been trying to write a macro that would automatically select the text in CSV and transform it into excel format. Normally this is easy (record macro), but I need to do that for a large spreadsheet and an error message occurs when I try to record the macro (Continuity chain too long)...

Here's the code :

 Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination := Range("A1"), DataType := xlDelimited,  _
        TextQualifier := xlDoubleQuote, ConsecutiveDelimiter := FALSE, Tab := FALSE,  _
        Semicolon := FALSE, Comma := TRUE, Space := FALSE, Other := FALSE, FieldInfo  _
        := Array(Array(1,1),Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1), _
        Array(7,1),Array(8,1),Array(9,1),Array(10,1),Array(11,1),Array(12,1),Array(13,1 _
        ),Array(14,1),Array(15,1),Array(16,1),Array(17,1),Array(18,1),Array(19,1),Array _
        (20,1),Array(21,1),Array(22,1),Array(23,1),Array(24,1),Array(25,1),Array(26,1), _
        Array(27,1),Array(28,1),Array(29,1),Array(30,1),Array(31,1),Array(32,1),Array( _
        33,1),Array(34,1),Array(35,1),Array(36,1),Array(37,1),Array(38,1),Array(39,1), _
        Array(40,1),Array(41,1),Array(42,1),Array(43,1),Array(44,1),Array(45,1),Array( _
        46,1),Array(47,1),Array(48,1),Array(49,1),Array(50,1),Array(51,1),Array(52,1), _
        Array(53,1),Array(54,1),Array(55,1),Array(56,1),Array(57,1),Array(58,1),Array( _
        59,1),Array(60,1),Array(61,1),Array(62,1),Array(63,1),Array(64,1),Array(65,1), _
        Array(66,1),Array(67,1),Array(68,1),Array(69,1),Array(70,1),Array(71,1),Array( _
        72,1),Array(73,1),Array(74,1),Array(75,1),Array(76,1),Array(77,1),Array(78,1), _
        Array(79,1),Array(80,1),Array(81,1),Array(82,1),Array(83,1),Array(84,1),Array( _
        85,1),Array(86,1),Array(87,1),Array(88,1),Array(89,1),Array(90,1),Array(91,1), _
        Array(92,1),Array(93,1),Array(94,1),Array(95,1),Array(96,1),Array(97,1),Array( _
        98,1),Array(99,1),Array(100,1),Array(101,1),Array(102,1),Array(103,1),Array(104 _
        ,1),Array(105,1),Array(106,1),Array(107,1),Array(108,1),Array(109,1),Array(110, _
        1),Array(111,1),Array(112,1),Array(113,1),Array(114,1),Array(115,1),Array(116,1 _
        ),Array(117,1),Array(118,1),Array(119,1),Array(120,1),Array(121,1),Array(122,1) _
        ,Array(123,1),Array(124,1),Array(125,1),Array(126,1),Array(127,1),Array(128,1), _
        Array(129,1),Array(130,1),Array(131,1),Array(132,1),Array(133,1),Array(134,1), _

But then it stops and says it can't continue. I have 11500 lines in Excel 2003.

Thanks in advance!


Hey Guys I Have I Q

I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work

1- Ranges Variables So I Can Choose The Range I Need
2- Look Up Table Variable So I Can Choose Between Tables I need
3- Color Every Range With Specific Color Through Drop Down List Color


  A B C D E F G H I J K L M N O P Q 1 A1 C 700     Range Tables Color   Table1   Table2   Table3 2 A2 C 701   First LookUp A1 A4 Table1 Blue Sky   A1 C 700   A1 Z 500   A1 Y 300 3 A3 C 702   Second LookUp A5 A13 Table3 Red   A2 C 701   A2 Z 501   A2 Y 301 4 A4 C 703   Third LookUp A14 A18 Table2 Yellow   A3 C 702   A3 Z 502   A3 Y 302 5 A5 Y 304               A4 C 703   A4 Z 503   A4 Y 303 6 A6 Y 305               A5 C 704   A5 Z 504   A5 Y 304 7 A7 Y 306               A6 C 705   A6 Z 505   A6 Y 305 8 A8 Y 307               A7 C 706   A7 Z 506   A7 Y 306 9 A9 Y 308               A8 C 707   A8 Z 507   A8 Y 307 10 A10 Y 309               A9 C 708   A9 Z 508   A9 Y 308 11 A11 Y 310               A10 C 709   A10 Z 509   A10 Y 309 12 A12 Y 311               A11 C 710   A11 Z 510   A11 Y 310 13 A13 Y 312               A12 C 711   A12 Z 511   A12 Y 311 14 A14 Z 513               A13 C 712   A13 Z 512   A13 Y 312 15 A15 Z 514               A14 C 713   A14 Z 513   A14 Y 313 16 A16 Z 515               A15 C 714   A15 Z 514   A15 Y 314 17 A17 Z 516               A16 C 715   A16 Z 515   A16 Y 315 18 A18 Z 517               A17 C 716   A17 Z 516   A17 Y 316 19                   A18 C 717   A18 Z 517   A18 Y 317 20                   A19 C 718   A19 Z 518   A19 Y 318 21                   A20 C 719   A20 Z519   A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)

Excel tables to the web >> Excel Jeanie HTML 4


In below table, in column B, I have used =LEFT(A2,FIND(" ",A2,1))
and in column C this formula =TEXT(VLOOKUP(LEFT(A2,FIND(" ",A2,1)),$F$1:$G$31,2,0),"dddd")

Problem is vlookup combined with left function doesn't fetch the data from the array.

If I write 1 , 2 ,3, on istead of left function in column C, I get the data. Now my question is if I only put numbers instead of formula, does vlookup work or not?

A B C 1 WD 1 #N/A 2 WD 2 #N/A 2 WD 2 #N/A 3 wd 3 #N/A 4 wd 4 #N/A 6 wd 6 #N/A 6 wd 6 #N/A 6 wd 6 #N/A 2 wd 2 #N/A 6 wd 6 #N/A 10 wd 10 #N/A Lst wd Lst #N/A



I am trying to do VLOOKUP in column A and fetch values from COLUMN B.

I have an array say A1 to A100. This array has repeated values.

for e.g.

A1 = 1 , B1 = Test1
A12 = 1, B12 = Test2
A33 = 1, B33 = Test3
A49 = 1, B49 = Test4

Now when I write the following formula


Then it returns TEST1. I have the following 2 questions.

1> Is there any way I get all four results when I try to search 1 ?

2> Is it possible that I get the last value in the array. i.e. Test4 in the above example.



I am trying to use vlookup to lookup an array of inputs, and then return an array of outputs. Then, I want to average over these outputs. I'd like to do all of this in one function call. Is it possible to do this, or to do something similar to achieve the same result?

I am trying to do it, and it's not working.

This is what my formula says:


cells B4:C6 contain the an array that looks like this:

a 1
b 2
c 3

Cells B9:B10 are as follows:


Thus, I want the vlookup to return the array {1,2}, average over those, and give 1.5. Instead, it returns 1.

My spreadsheet is enclosed. As you can see, B13 returns 1 instead of 1.5.


I am trying to use the VLOOKUP function in an array context. I tried changing the following formula where the first argument is an a constant array but it does not seem to work properly. Does anyone have any ideas or suggestions?





Dear All,

Please can you offer me some advice.

Say I wanted to lookup value A5, and return all columns in the range A1:D5, I could use the formula =vlookup(A5,$A$1:$D$5,2,false) and copy this formula 3 times changing the third parameter 2, to 3 , 4 and 5. i.e 4 VLOOKUP formulas.

I could, instead, convert the VLOOKUP into an array and enter it CSE i.e =vlookup(A5,$A$1:$D$5,{2,3,4,5},false). This returns the right answer but "appears" to have only used 1 VLOOKUP.

What if this was extended to say 26 columns so instead of "hard coding" the array constants I used =vlookup(A5,$A$1:$D$5,COLUMN(B:AA),false). Is this better than 26 indivisual VLOOKUP's in term of speed?

Many thanks in anticipation,


I have a colleague who is attempting to create a macro to import a text file into an Excel spreadsheet. The text file is comma delimited, and it is VERY wide: once converted into Excel it used EVERY column (right up to column IV). The number of rows is variable.

The problem is that when my colleague attempts to record the macro to open this file, he gets an error message "Too Many Line Continuations".

This is how the macro looks once the error has appeared:

Workbooks.OpenText Filename := _
"G:\Concur\In\extract_CASH_GL_V2_p0001358hmnx_20070719010046.txt", Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier := xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE, Comma := FALSE _
, Space := FALSE, Other := TRUE, OtherChar := "|", FieldInfo := Array(Array(1,1 _
),Array(2,1),Array(3,1),Array(4,1),Array(5,1),Array(6,1),Array(7,1),Array(8,1), _
Array(9,1),Array(10,1),Array(11,1),Array(12,1),Array(13,1),Array(14,1),Array(15 _
,1),Array(16,1),Array(17,1),Array(18,1),Array(19,1),Array(20,1),Array(21,1), _
Array(22,1),Array(23,1),Array(24,1),Array(25,1),Array(26,1),Array(27,1),Array( _
28,1),Array(29,1),Array(30,1),Array(31,1),Array(32,1),Array(33,1),Array(34,1), _
Array(35,1),Array(36,1),Array(37,1),Array(38,1),Array(39,1),Array(40,1),Array( _
41,1),Array(42,1),Array(43,1),Array(44,1),Array(45,1),Array(46,1),Array(47,1), _
Array(48,1),Array(49,1),Array(50,1),Array(51,1),Array(52,1),Array(53,1),Array( _
54,1),Array(55,1),Array(56,1),Array(57,1),Array(58,1),Array(59,1),Array(60,1), _
Array(61,1),Array(62,1),Array(63,1),Array(64,1),Array(65,1),Array(66,1),Array( _
67,1),Array(68,1),Array(69,1),Array(70,1),Array(71,1),Array(72,1),Array(73,1), _
Array(74,1),Array(75,1),Array(76,1),Array(77,1),Array(78,1),Array(79,1),Array( _
80,1),Array(81,1),Array(82,1),Array(83,1),Array(84,1),Array(85,1),Array(86,1), _
Array(87,1),Array(88,1),Array(89,1),Array(90,1),Array(91,1),Array(92,1),Array( _
93,1),Array(94,1),Array(95,1),Array(96,1),Array(97,1),Array(98,1),Array(99,1), _
Array(100,1),Array(101,1),Array(102,1),Array(103,1),Array(104,1),Array(105,1), _
Array(106,1),Array(107,1),Array(108,1),Array(109,1),Array(110,1),Array(111,1), _
Array(112,1),Array(113,1),Array(114,1),Array(115,1),Array(116,1),Array(117,1), _
Array(118,1),Array(119,1),Array(120,1),Array(121,1),Array(122,1),Array(123,1), _
End Sub

I don't know if this displays in the same way here as it does in the VBA editor but all this is spread over 14 lines.

I'm sure the problem relates to the fact that we are attempting to chop this file into 256 columns.

Does anyone have any suggestions as to how we can overcome this limitation?

Thanks & Regards,

using vlookup I want to do the following, I have several table arrays defined in my workbook. Exemple of names: smb, bookings, revenue. I want to build a vlookup that takes it array depending of what is filled in in a cell. Let's say the array name is filled in in cell B1, the lookup value in A1, the vlookup will be: =vlookup(A1;B1;2;false). But this doesn't seem to work as it brings back #N/A.
Any ideas, any other function that can help ?

Thanks in advance,


I have a table array ,i want to take the details from this array to another sheet
(please visit the link to view the pictu

In the next sheet i have given the vlookup function as ; =vlookup(D3,sheet2!B2:M12,3) .
and in the column D3, i have the value , if the value is in the array , this function is working properly , but if the value that i given in the column D3 is wrong , then the function is showing some other results. My question is , when if we given the value is not in the array , then the functioned column should be null or n/a . Here i am giving the second sheet picture please visit for your reference .

Please help me to rectify this error .

In Column A1:A10 I have a really long series of alpha numberic digits in each cell.

I use this macro with text to column to split them up for me into different columns.

The problem I have is that after they go through this conversion all of the fractions in columns L are turned into dates.

I have no idea how to fix this.


    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(9, 1), Array(10, 1), Array(22, 1), Array(34, 1), _
        Array(35, 1), Array(40, 1), Array(72, 1), Array(88, 1), Array(104, 1), Array(109, 1), Array _
        (119, 1), Array(122, 1), Array(127, 1), Array(151, 1), Array(156, 1), Array(161, 1), Array( _
        165, 1), Array(177, 1), Array(202, 1), Array(204, 1), Array(214, 1), Array(223, 1), Array( _
        226, 1), Array(235, 1), Array(267, 1), Array(272, 1), Array(282, 1), Array(291, 1), Array( _
        307, 1), Array(323, 1), Array(324, 1), Array(325, 1), Array(326, 1), Array(331, 1), Array( _
        336, 1), Array(346, 1), Array(386, 1), Array(426, 1), Array(466, 1), Array(468, 1), Array( _
        478, 1), Array(494, 1), Array(504, 1), Array(509, 1), Array(525, 1), Array(589, 1), Array( _
        599, 1), Array(604, 1), Array(605, 1), Array(606, 1), Array(607, 1), Array(608, 1), Array( _
        632, 1), Array(664, 1), Array(696, 1)), TrailingMinusNumbers:=True
End Sub

How do I format it so that the new columns that are made are automatically formatted to be Text?

I am using Excel 2003.

Hi all, trying to do an array vlookup to return a result for every row hit

a b c d
1 x 1 2 3
2 y 4 5 7
3 x 7 8 9

So I want a vlookup that returns 3 and 9 when I search x

My understanding of vlookup arrays is that I know how to get for example the 2 and 3
{=vlookup("x",a1:d3,{3;4},0)} over 2 cells
but that is because its returning multiple results of a single HIT
Im trying to get multiple HIT's if you get my drift
Thanks, Anth.

I'm stuck on a problem, which may not be solvable by Excel. I have a long column of data that I need to run through a VLOOKUP or INDEX with a table I made. Seems like VLOOKUP and INDEX don't recognize when I put an array in the first part of the formula and treat only the first value of the array as the input. Is there any way to have the formula I made return the formula I want?
Any creative alternatives?

Hypothetical Table:

ID2 ID 0 1 2 3 1 0.1 0.1 0.1 0.1 2 0.2 0.2 0.2 0.2 3 0.31 0.31 0.31 0 4 0 0 0.05 0.05 5 0.005 0.005 0.005 0.005
Data to run through table (In reality, these columns are about 3000 entries long)


ID2 1

2 2


The formula:


Returns .1 not .61 as I would like it to.

I have figured out a way to solve this with an intermediary table, but I would love to be able to use only one cell in the solution to this.


Hi all,

I have this array formula which I will be using to sum an overall total:



What I would also like to do is run a VLOOKUP formula in another sheet (lets call it sheet2) where values in column A (name) are the reference for the VLOOKUP. I then just want to return a result for the row starting with that name in sheet1.

Frankly VLOOKUP confuses me a little, and without the help of the mighty Ron the above formula wouldn't even exist!

Any help greatfully received!