Range referencing in VLOOKUP function, string concatenate?

I am attempting to perform a vlookup in an array, located among a
group of 21 other arrays.  So, each array has a name beside it, but
the arrays all contain Similar, and sometimes identical data.  I first
use a MATCH function to find the name and its location on the
worksheet containing all the arrays.  I now desire to embed the match
function in my vlookup along these lines...

=vlookup(AJ4,Jobs!"B"&MATCH(AJ3,Jobs!A2:A2123)+2:Jobs!"W"&MATCH
(AJ3,Jobs!A2:A2123)+100)

where AJ3 contains the name i am initially looking up, and AJ4
contains the value I wish to find in the chosen array on worksheet
Jobs.  So if the name is Thomas, and match returns 809, I want Vlookup
to search the range B811:W909

If anyone can show me where I am going wrong it will be most
appreciated.

Thank you.
0
1/31/2009 1:27:38 AM
excel 39879 articles. 2 followers. Follow

4 Replies
361 Views

Similar Articles

[PageSpeed] 4

You should be able to do it this way:

=3Dvlookup(AJ4,INDIRECT("Jobs!B"&MATCH(AJ3,Jobs!A$2:A$2123)+2&":W"&MATCH
(AJ3,Jobs!A$2:A$2123)+100),col,0)

where col is the column within B811:W909 that you want to return the
corresponding data from.

Hope this helps.

Pete

On Jan 31, 1:27=A0am, Russ3Z <jrthomas1...@gmail.com> wrote:
> I am attempting to perform a vlookup in an array, located among a
> group of 21 other arrays. =A0So, each array has a name beside it, but
> the arrays all contain Similar, and sometimes identical data. =A0I first
> use a MATCH function to find the name and its location on the
> worksheet containing all the arrays. =A0I now desire to embed the match
> function in my vlookup along these lines...
>
> =3Dvlookup(AJ4,Jobs!"B"&MATCH(AJ3,Jobs!A2:A2123)+2:Jobs!"W"&MATCH
> (AJ3,Jobs!A2:A2123)+100)
>
> where AJ3 contains the name i am initially looking up, and AJ4
> contains the value I wish to find in the chosen array on worksheet
> Jobs. =A0So if the name is Thomas, and match returns 809, I want Vlookup
> to search the range B811:W909
>
> If anyone can show me where I am going wrong it will be most
> appreciated.
>
> Thank you.

0
pashurst (2576)
1/31/2009 1:35:29 AM
Excellent, that did the trick.  Thank you kindly for the helpful and
timely response.
0
1/31/2009 1:39:09 AM
You're welcome, Russ - thanks for feeding back.

Now it's my bedtime.

Pete

On Jan 31, 1:39=A0am, Russ3Z <jrthomas1...@gmail.com> wrote:
> Excellent, that did the trick. =A0Thank you kindly for the helpful and
> timely response.

0
pashurst (2576)
1/31/2009 1:43:01 AM
Hi,

A somewhat simplier approach might be something like this:

=VLOOKUP(AJ4,OFFSET(Jobs!A2,MATCH(AJ3,Jobs!A4:A2125),,100,23),3,)

Where 3 is the column argument I have choosen for this example.

You will probably need to adjust some of the numbers a little, but this is 
the basic idea.  And quite a bit shorter.

-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Russ3Z" wrote:

> I am attempting to perform a vlookup in an array, located among a
> group of 21 other arrays.  So, each array has a name beside it, but
> the arrays all contain Similar, and sometimes identical data.  I first
> use a MATCH function to find the name and its location on the
> worksheet containing all the arrays.  I now desire to embed the match
> function in my vlookup along these lines...
> 
> =vlookup(AJ4,Jobs!"B"&MATCH(AJ3,Jobs!A2:A2123)+2:Jobs!"W"&MATCH
> (AJ3,Jobs!A2:A2123)+100)
> 
> where AJ3 contains the name i am initially looking up, and AJ4
> contains the value I wish to find in the chosen array on worksheet
> Jobs.  So if the name is Thomas, and match returns 809, I want Vlookup
> to search the range B811:W909
> 
> If anyone can show me where I am going wrong it will be most
> appreciated.
> 
> Thank you.
> 
0
1/31/2009 5:36:00 AM
Reply:

Similar Artilces:

Connection String
Hi... Our company just purchased some propriety software that is on a server running MSSql. I want to connect to it to retrieve data only. I can connect using ODBC through Access 2000, but each time I try to use the tables, I have to supply the password for the program. I want to use VB6.0 to connect to the database, but can't figure out the connection string. An example provided in VB6.0 is: wrkODBC.OpenConnection("Connection1", , , _ "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=publishers") (what reference do I need in the Project to be sure what I ...

Calendar Formatting Date Range problem
Hi, I am trying to make a monthly calendar that our administrator can update easily with new dates, and mail out. I've formatted the calendar and it looks great as it is right now for the month of April. However, when I choose "change date range" it reverts back to the original formatting and I have to start all over. I realize this is because I used a "design object". However, if I don't use a design object, then I can't change the date ranges. How do I change the date range on my calendar without losing all the formatting changes I made? Thanks T...

Time functions #2
Hi, I have to do some time calculations and I have a formula that outputs in minutes and I have to subtract a standard time to this number of minutes. For example, I have 8:00 AM in one cell and 20.68 minutes on an other cell. The output that I am expecting is 7:39 AM. Does anybody know how to bring an output like that by subtracting the minutes? Please help. Thanks With 8:00 AM in A1 and 20.68 in B1, use this formula =A1-B1/(24*60) Why does it work? Because Excel stores time as a fraction of a day. We convert minutes to days by dividing by 24*60 (24 hr/day and 60 min/hr) You could just u...

Getting row indexes on Range
(I refer to C# code, but answers in VB are welcome) I have a Range in Excel, which includes several cells (the cells the user selected in the Excel sheet). The range might include the following cells A2, B7, G4. This means that the cells might not be connected. If I look at myRange.Cells.Count, it will return 3. If I look at myRange.Row, it will return 2 (if A2 was the first selected row by the user). Now, I need to get the row numbers of all selected rows, so in the above range, I need an int[] of {2, 7, 4}. But I can't see any solution to go through the Cells and get t...

create chart from non adjacent range
I need to know how to do this Select one area, then hold CTRL while selecting the next area, etc. Then run the Chart Wizard as usual. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "rita" <rita@discussions.microsoft.com> wrote in message news:18C9672C-0EAB-401C-8798-4C51158EAF8B@microsoft.com... >I need to know how to do this ...

>> Calendar Control drag to select range
Hi, using MS Access 2003, is it possible to allow a user to drag to select a range of dates and, if 'yes', how do you store the selected date range? Many thanks, Jonathan Not that I know of. Storing a range of dates is quite simple though. You store the start date and the end date and use a calculation to show all the dates between. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:4F22299F-3765-40D2-AA03-67FB42FFC07A@microsoft.com... > ...

Adding functionality to MS Outlook Express
Hi, I am looking for help to integrate anti-spam open source programs with MS Outlook Express :- - SpamPal - http://www.spampal.org/ - Spamato - http://www.spamato.net/ The problem is to provide a library that will facilitate this. I have read the MSDN Messaging and Collaboration documentation which gives the ability to get notifications of new messages and to access message folders :- http://msdn2.microsoft.com/en-gb/library/ms709546.aspx The next need is to add a toolbar (with buttons for SPAM/NOSPAM) and to detect what messages are selected in the message window. The MSOE...

Inserted rows, now need Counta function
Hi I've got a data dump. I've figured out how to insert a blank row after a change in name in column A and insert "Total" - so... bill.... bill.... bill Total - bob.... bob.... bob.... bob Total - What I need in column C next to total is to insert the COUNTA function for each person. Any ideas? cheers You say your code inserts a blank row after a change in name and inserts "Total", but your example doesn't show this. Can you post the code you are using to do your "insert"? It will probably be easier to handle the ...

Can't get Date Range to work from Form to Report
Hi, I've followed Allen Browne's directions very closely for creating a form for a date range to limit results to that date range on a report and can not get it to work for some reason. I've read many threads on this board which all say the same thing and it seems to work for everyone else. :( Here's what I have: A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. The form is called "frmDateRange" and the report is called "Sales Activity Report". The two unbound text boxes are set to "Short Date" format which the user ...

Vlookup edited
Hello all you wonderfulhelp, Is it possible to avoid "NA" when using "vlookup" function. I need info only where it brings results. Thank you -- smile =IF(ISERROR(VLOOKUP(B1,C1:D4,2)),"",VLOOKUP(B1,C1:D4,2)) -- Gary''s Student - gsnu200851 Example of using ISNA rather than ISERROR which hides all errors. =IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G1,$A$1:$F$31,2,FALSE)) Gord Dibben MS Excel MVP On Thu, 7 May 2009 09:26:02 -0700, israel <israel@discussions.microsoft.com> wrote: >Hello all you wonderfulhelp, > >...

If Function Possibilities
Is there a way to set up in a cell A1, something like... If the formula used in cell B1 is "here I would type the actual formula in," then display the words "works," else display "mistake" ? -- rheller ------------------------------------------------------------------------ rheller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24081 View this thread: http://www.excelforum.com/showthread.php?threadid=377045 That's a pretty cryptic post. Do you mean: In A1: =IF(B1=n,"Works","Mistake") where 'n...

Searching in a column of strings
Hi I try to find how many cells have a sequence of charcters. From Cell A1:A50. Exemple for "NY" : cellA1 = NY123 cellA2 = NY3235 cellA3 = NY5434 cellA4 = LA4234 function(NY) = 3 Like Seach but instead to be in a single cell, it's in a sequence of cell? Thanks Jack Jack =COUNTIF($A:$A,"*NY*") Regards Trevor "Jack" <anonymous@discussions.microsoft.com> wrote in message news:348501c3fd71$700c6c60$a001280a@phx.gbl... > > Hi > > I try to find how many cells have a sequence of > charcters. From Cell A1:A50. > > Exe...

Unlocking named ranges
I am working with a spreadsheet created by a former employee. He used named ranges a lot. I am trying to understand the worksheet. When I see an equation in a cell like =Sum(Shipments) I feel I know what it means. But how do I learn what the actual cell reference is ie = Sum(D2:G2).? TIA Insert a new, empty worksheet into the workbook. Then you can use The Insert-> Name-> Paste menu option and click on the Paste List button to get a listing of all the names and the definitions "JohnL" wrote: > I am working with a spreadsheet created by a former employee. He used...

Writing Access functions
I am an old FoxPro programmer (but have been out of programming for many years) and I have been through the Access basics that I have been able to find on line. So I can create tables, forms enter data the basics. I am learning VBA no problem there. Now I want to write some functions. I would like to write one function that is passed an ID and returns the last name of the person having that ID. I would like to write this function in two ways: 1. Connect to a table in my DB find the ID and return the name 2. Have the function perform a query that returns a record set from which t...

Automatic Dynamic Chart Ranges
Hi: I have an XY plot that refers to a set range of values on my worksheet In this case, it is 8 rows long. I chose 8 rows because that is th maximum number of data points that I will ever need. I have set up th chart values so that they are dynamically calculated based on VLOOKUP from other data. Sometimes there are only 2 data points, sometimes or 6. The problem is that no matter how I set up the formulas, Exce does not like the cells without values in them being included in th chart series (I have hardwired it to include all 8 rows). I thought i might ignore a blank ("" in ...

reading file name from folder function
I am using Access 97. Just wonder if there is a fuction which can read all the file name from a folder and put it to a column in a table. Please let me know. Thanks. Hi. >I am using Access 97. Just wonder if there is a fuction which can read all >the file name from a folder and put it to a column in a table. Please see the getFileInfo( ) function in the tip, "How to compare the contents of two directories," on the following Web page for an example: http://www.access.qbuilt.com/html/vba1.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http...

Function Keys in Excel
Can someone please tell me what the F2 key does in Excel 2000? I have a column of data which has been copied from Access 2000 into an Excel 2000 sheet. It is numeric data and when I use the "Countif" function on this data, it doesn't calculate. However, if I press the "F2" function key in any of the cells, something happens to the data so that the "countif" function recoginises it. Can anyone explain what is happening here? Thank you. -- LPS F2 allows you to edit directly in the cell, do you have calculations set to automatic under tools>calcul...

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

Named Ranges
Hi! I created several worksheets that contained huge lists, so I created a dynamic named range. Now that the name has been inserted in my array formulas, my Macro runs very slowly. The Macro unprotects one of my sheets, sorts the data in a table, hides the blank rows and the protects the sheet again. There are no related named range formulas in that worksheet, so I'm not sure why it would be affected. The Macro ran quickly when I had a defined range of data. Would giving a named range like 'Date' and having text in my workbook with 'Date' create conflicts? Could this...

print only range that contain information in excel 2007
i have a large worksheet to cover a large number of possible inserted entries, all cells show blank until entries are copied and pasted in the worksheet. The amount of entries vary. Is there a way to set the workseet to print only the range of cells with information in them ...

faq: type of CArray as function parameter create errors
Hi, class CMyPoint { public: long x; long y; CArray<int,int&> m_intSegmentNo; CMyPoint(); virtual ~CMyPoint(); CMyPoint(const long &xx, const long &yy); CMyPoint(const CMyPoint &cmpt); CMyPoint(const CPoint &cpt); CMyPoint& operator = (const CMyPoint &assmypt); CMyPoint& operator = (const CPoint &asspt); bool operator == (const CMyPoint &rmypt);//const CMyPoint &lmypt, bool operator != (const CMyPoint &rmypt);//const CMyPoint &lmypt, }; .... class CSegment { public: CList<CMyPoint,CMyPoint&> m_PointList; CSeg...

Function for convertion of leters doesn't work.
I'm using function LOWER to convert string i my table on small size but Access gives me a message that the function is not defined.Please, do you know another function? MS Access does not have a LOWER function. Try LCase() instead. -- Cheryl Fischer Law/Sys Associates Houston, TX "Stenli" <svet2000@seznam.cz> wrote in message news:10b201c3626e$b613a050$a301280a@phx.gbl... > I'm using function LOWER to convert string i my table on > small size but Access gives me a message that the function > is not defined.Please, do you know another function? ...

Report Writer Functions #2
Where can I find instructions and examples on the various report writer functions? I've installed the SDK but I don't see anything there. The Report Writer On-line manual (Help - Printable Manuals - Reporting - Report Writer) has a fairly good description of the functions in Chapter 6. Do you look there or is there a specific function that you did not find there for which you are searching? "Elaine" wrote: > Where can I find instructions and examples on the various report writer > functions? I've installed the SDK but I don't see anything there. Hi El...

Ranges
Hi, I have some records on a spreadsheet, I want to select them with a macro the range is A1 to m1 down to however many records there happen to be? how do I specify this for my excel macro? Thanks, Jim. Jim try this to get the end Dim iLastRow As Long, liLastCol As Long On Error Resume Next iLastRow = 1: iLastCol = 1 With ActiveSheet.Range("A:M") iLastRow = .Find("*", .Cells(1), xlFormulas, _ xlWhole, xlByRows, xlPrevious).Row iLastCol = .Find("*", .Cells(1), xlFormulas, _ xlWhole, xlByColumns, xlPrevious)...

Question on "Value" function
In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? Hi this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Ap...