VLOOKUP: Retrieving Values

Vlookup:
I am trying to lookup a value ( SCOTLAND ) on a spreadsheet called (
Data ) in order to populate ceratin cells on my other spreadsheet (
Spreadone ).
If the value (SCOTLAND) is located in (Data) I want it to copy the
values of specific cells (not all of them ) from the same row into (
Spreadone ).

So :
CHECK COLUMN N (CLASS) IN SPREADSHEET (DATA) FOR STRING (SCOTLAND)
IF THE STRING = SCOTLAND THEN

IN SPREADSHEET (Spreadone) POPULATE

CELL A7:A25 =   ACCOUNT NO (from Data)
CELL B7:B25 =   CLIENT (from Data)
CELL C7:C25 =   CUSIP (from Data)
CELL D7:D25 =   QTY (from Data)
CELL E7:E25 =   AMOUNT (from Data)
CELL F7:F25 =   CCY (from Data)
CELL G7:G25 =  USDEQUIV (from Data)
CELL H7:H25 =   AGE (from Data)
CELL I7:I25 =      LAST CHANGE (from Data)

A                B              C        D           E             F
     G                H          I
ACCOUNT  CLIENT   CUSIP   QTY     AMOUNT   CCY    USDEQUIV  AGE   Last
change
39100284    EXCEL   999995  13.00      43.00      GBP     9.00
   21            3
29330009    EXCEL   12234    10            35.00    GBP     22.00
     12           10

Please help as i am trying to write some VB code to do this and am
failing miserably... Thanks

0
12/19/2005 6:08:34 PM
excel 39879 articles. 2 followers. Follow

7 Replies
873 Views

Similar Articles

[PageSpeed] 31

Hi Mellowe,

Select nine cells in the row you want the info to be displayed and enter 
this formula while the cells are still highlighted.  Hit control + shift + 
enter (all at once) that's an array-enter.  You will get { } around your 
formula if done correctly, don't type them in

Where A5 is cell you would enter the lookup value, Scotland, in your 
example, and A1:J3 is the table.

=VLOOKUP(A5,A1:J3,{2,3,4,5,6,7,8,9,10},0)

HTH
Regards,
Howard

"mellowe" <melanie_lowe1@hotmail.com> wrote in message 
news:1135015714.618827.203030@g44g2000cwa.googlegroups.com...
> Vlookup:
> I am trying to lookup a value ( SCOTLAND ) on a spreadsheet called (
> Data ) in order to populate ceratin cells on my other spreadsheet (
> Spreadone ).
> If the value (SCOTLAND) is located in (Data) I want it to copy the
> values of specific cells (not all of them ) from the same row into (
> Spreadone ).
>
> So :
> CHECK COLUMN N (CLASS) IN SPREADSHEET (DATA) FOR STRING (SCOTLAND)
> IF THE STRING = SCOTLAND THEN
>
> IN SPREADSHEET (Spreadone) POPULATE
>
> CELL A7:A25 =   ACCOUNT NO (from Data)
> CELL B7:B25 =   CLIENT (from Data)
> CELL C7:C25 =   CUSIP (from Data)
> CELL D7:D25 =   QTY (from Data)
> CELL E7:E25 =   AMOUNT (from Data)
> CELL F7:F25 =   CCY (from Data)
> CELL G7:G25 =  USDEQUIV (from Data)
> CELL H7:H25 =   AGE (from Data)
> CELL I7:I25 =      LAST CHANGE (from Data)
>
> A                B              C        D           E             F
>     G                H          I
> ACCOUNT  CLIENT   CUSIP   QTY     AMOUNT   CCY    USDEQUIV  AGE   Last
> change
> 39100284    EXCEL   999995  13.00      43.00      GBP     9.00
>   21            3
> 29330009    EXCEL   12234    10            35.00    GBP     22.00
>     12           10
>
> Please help as i am trying to write some VB code to do this and am
> failing miserably... Thanks
> 


0
lhkittle (223)
12/19/2005 7:00:19 PM
Take a 2-step approach.
1. Establish the line number of the corresponding item in the table in DATA. 
Use MATCH() for that, 3rd argument should be zero.
2. Use 9 INDEX() functions per line to retrieve the items from the line you 
found in step 1

Look in HELP for details, post again if this same thread if you have 
problems

-- 
Kind regards,

Niek Otten


"mellowe" <melanie_lowe1@hotmail.com> wrote in message 
news:1135015714.618827.203030@g44g2000cwa.googlegroups.com...
> Vlookup:
> I am trying to lookup a value ( SCOTLAND ) on a spreadsheet called (
> Data ) in order to populate ceratin cells on my other spreadsheet (
> Spreadone ).
> If the value (SCOTLAND) is located in (Data) I want it to copy the
> values of specific cells (not all of them ) from the same row into (
> Spreadone ).
>
> So :
> CHECK COLUMN N (CLASS) IN SPREADSHEET (DATA) FOR STRING (SCOTLAND)
> IF THE STRING = SCOTLAND THEN
>
> IN SPREADSHEET (Spreadone) POPULATE
>
> CELL A7:A25 =   ACCOUNT NO (from Data)
> CELL B7:B25 =   CLIENT (from Data)
> CELL C7:C25 =   CUSIP (from Data)
> CELL D7:D25 =   QTY (from Data)
> CELL E7:E25 =   AMOUNT (from Data)
> CELL F7:F25 =   CCY (from Data)
> CELL G7:G25 =  USDEQUIV (from Data)
> CELL H7:H25 =   AGE (from Data)
> CELL I7:I25 =      LAST CHANGE (from Data)
>
> A                B              C        D           E             F
>     G                H          I
> ACCOUNT  CLIENT   CUSIP   QTY     AMOUNT   CCY    USDEQUIV  AGE   Last
> change
> 39100284    EXCEL   999995  13.00      43.00      GBP     9.00
>   21            3
> 29330009    EXCEL   12234    10            35.00    GBP     22.00
>     12           10
>
> Please help as i am trying to write some VB code to do this and am
> failing miserably... Thanks
> 


0
nicolaus (2022)
12/19/2005 7:00:28 PM
Thanks for the advice, but a part of my problem is that my speadsheet
called DATA is different every day - therefore my primary key would be
if a cell in column N on spreadsheet 'DATA' contains 'SCOTLAND' (e.g
N2), then from the same row of the occurence ,copy the content of cell
G2 to cell A7 in speadsheet called Spreadone, copy cell J2 from (DATA)
to B7 in (Spreadone) , copy cell L2 from (DATA) to C7 in (Spreadone)
etc. And there could be many cells in my spreadsheet called 'DATA' that
contains 'SCOTLAND' that I need to have the cells from the same row
copied to spreadsheet 'Spreadone'. Can this be done?

0
12/20/2005 9:15:27 AM
Hi

You could mark the row of headers in Data, and Data>Filter>Autofilter
Use the dropdown on column N to Select Scotland
Copy the resulting range of visible rows
Paste to sheet Spreadone.

Alternatively, you could use Advanced Filter, starting from Spreadone and 
setting the criteria as Scotland. For help on doing this take a look at 
Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs


Regards

Roger Govier


mellowe wrote:
> Thanks for the advice, but a part of my problem is that my speadsheet
> called DATA is different every day - therefore my primary key would be
> if a cell in column N on spreadsheet 'DATA' contains 'SCOTLAND' (e.g
> N2), then from the same row of the occurence ,copy the content of cell
> G2 to cell A7 in speadsheet called Spreadone, copy cell J2 from (DATA)
> to B7 in (Spreadone) , copy cell L2 from (DATA) to C7 in (Spreadone)
> etc. And there could be many cells in my spreadsheet called 'DATA' that
> contains 'SCOTLAND' that I need to have the cells from the same row
> copied to spreadsheet 'Spreadone'. Can this be done?
> 
0
roger1272 (620)
12/20/2005 10:22:14 AM
Again this was really helpful but i dont think this will apply in this
case as using:
 Data>Filter>Autofilter and drop down for 'Scotland' is ok manually but
I really wanted this in a macro so the user doesnt have to search
everything out and manually copy and paste the relevant cells to
another spreadsheet.And Using:
 Advanced Filter will copy the entire row into another spreadsheet and
I cant see how to use this to filter for 'Scotland' first then once all
cells in column E with Scotland in them is found ; only copy cells
G,K,M,N and P from the same row into the other spreadsheet.

One way I have been looking at this is with this formula:
 =VLOOKUP($H$3,'[Spreadone.xls]Data'!E$2:N$250,3,FALSE) which does find
the occurence of Scotland in 'Data' and populates the first cell in
'Spreadone' with the correct number but when this formula is dragged
down the column it just populates with the same number I want it to
look up the next number etc and populate correclty - Am I able to amend
this formula somehow? thanks for the help!

0
12/20/2005 3:19:43 PM
Sorry the formula I meant to write was:
=VLOOKUP($H$3,'Data'!$E2:$N$250,3,FALSE)
Which does populate the correct account number in the cell on
'Spreadone' when cell E contains 'SCOTLAND' but when there is no
'SCOTLAND' in cell E in 'Data' it just duplicates the last number:
Column A
Account
391000
356788 **
356788 **
124455
Please help as I am slowly going mad!

0
12/20/2005 4:02:23 PM
Hi

As I said, Vlookup will not do this for you.

Advanced Filter sounds your best bet.
If you don't want all of the data to show on the second sheet, then just 
Hide the columns you don't want.
If you switch on the macro recorder as you carry out this task, then you can 
capture the steps as a macro, for others to use when required.

Try downloading the workbook http://www.contextures.com/AdvFilterSearchWord.zip
and see if you can adapt that to suit your needs.
It is filtering to the same sheet, but could easily be amended to go to 
another sheet, and you could then hide the columns not required as mentioned 
above.

Regards

Roger Govier


mellowe wrote:
> Again this was really helpful but i dont think this will apply in this
> case as using:
>  Data>Filter>Autofilter and drop down for 'Scotland' is ok manually but
> I really wanted this in a macro so the user doesnt have to search
> everything out and manually copy and paste the relevant cells to
> another spreadsheet.And Using:
>  Advanced Filter will copy the entire row into another spreadsheet and
> I cant see how to use this to filter for 'Scotland' first then once all
> cells in column E with Scotland in them is found ; only copy cells
> G,K,M,N and P from the same row into the other spreadsheet.
> 
> One way I have been looking at this is with this formula:
>  =VLOOKUP($H$3,'[Spreadone.xls]Data'!E$2:N$250,3,FALSE) which does find
> the occurence of Scotland in 'Data' and populates the first cell in
> 'Spreadone' with the correct number but when this formula is dragged
> down the column it just populates with the same number I want it to
> look up the next number etc and populate correclty - Am I able to amend
> this formula somehow? thanks for the help!
> 
0
roger1272 (620)
12/20/2005 5:47:22 PM
Reply:

Similar Artilces:

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

how to plot ONE x-y curve of two datas with different x values?
I have this data: x: (1.1) (3.6) (6.0) (7.5) (9.2) ... y: (4.2) (5.6) (6.3) (9.4) (9.6) ... and this data: x: (2.3) (3.2) (5.4) (6.7) (7.2) ... y: (1.2) (2.4) (3.3) (6.4) (8.9) ... Now I want a x-y chart with a SINGLE smooth curve, showing the average result of these two datas. By average, I mean that, suppose I have two curves, I want a curve which is always in the middle of them. As you can see, my problem is that the x data is not very regular, and the x data of both datas usually do not coincide. Thanks! You need to create a single table of all your x values, and interpolate to find th...

change value with report
I have a report that has worked fine for years on my system. The query is based on one yes/no check mark on about 10 records every quarter but now it has 50 records. To much to change by hand. After I run the report I need to set this field to false and set a second field to true. Can someone tell me were to look to find a solution Thank You Stephen Use the report's Close event to run an update query that will reset those fields' values for you. -- Ken Snell <MS ACCESS MVP> "Stephen" <smile_inspector@hotmail.com> wrote in message news:uwj1zWhiIH...

#VALUE! #2
Can anyone answer this: When i paste in info from a web site, one of the columns contains numbers, yet when I try to refer to those numbers in formulas in other cells, it always comes up with the #VALUE! message, as though it were text in there, not numbers. Any ideas? Please refrain from multiposting, for a possible answer see your post in the worksheet.functions NG -- Regards, Peo Sjoblom "Cam" <kingsandmadmen@hotmail.com> wrote in message news:2c43601c39384$b28da7e0$a601280a@phx.gbl... > Can anyone answer this: When i paste in info from a web > site, one of...

Newton Funds showing incorrect values
Previously it has been identified that there's a problem when downloading values for Newton Funds. The suggestion was that this wasn't unusual and would be corrected. However, it's still a problem and I wondered whether anyone knew when the correction was likely to happen! I'm using MONEY 2004 on Windows XP No, we don't know. It might be worth raising an UK MSN Money problem ticket so they know you're waiting on it, if you haven't already. (http://money.mvps.org/faq/article/322.aspx) -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org/ Check http:...

Mouse Move response based on combo box value
Hello, all. I have been asked to add a feature to an exiting form where a "note" will appear on mouse over only if a combo box of a continuous form is a particular value (i.e. Annual Safety Training). The main form is frmEmployees, the subform is subformClassesAttended. The combo box is Combo12, but it is displaying the text description (colmun 2) associated with a bound field of a number type, classID. I have a hidden label, lblInfo, with the info to be displayed but am not sure of the coding & whether I should be referencing the description in Combo12 or Class...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Toggle switch between absolute and relative values creating a macr
Hello, Why is it that when creating a macro sometimes I see the toggle switch for absolute/relative values and sometimes I don't? Actually, I no longer see the toggle switch when recording a new macro. What do I have to do to make it reappear? Thank you. "Al" wrote: > Hello, > Why is it that when creating a macro sometimes I see the toggle switch for > absolute/relative values and sometimes I don't? Al Tools>Customize>Toolbars. Select "Stop Recording" toolbar>Close Stop Recording Toolbar should now be visible on sheet. Hit "X"...

Search for cell value
Is it possibly to look at cell value and if in a range to place the record # in a cell ? E.g. Value in A2 that I need to know if in a range "R1234JK-3" RANGE Record # Proj # Billing # Review # 1 R1234JK-3 KIKII-87 KL12367 2 RM334 12456 0089JK_9 Ce;; Value returned in A3 "1" which stands for record 1 I have interpreted this as follows; You have data such as Record # Proj # Billing # Review # 1 R1234JK-3 ...

retrieve unique data
I would greatly appreciate it if someone could assist me with this. I have a file with massive amounts of data: STATE INVOICE AMOUNT NY 123 $100 NY 34A 25 CA 4453A1 75 NV F123 75 NV B678 45 SD 123 56 AZ 34A 100 >From this file, I would like to use a formula that would pull data by STATE to individual sheets without using macros. So, a sheet for NV would retrieve F123 $75 and B678 $45. What Excel function should I use? When ...

Controling how zero values are plotted in charts
Can anybody advise me how to overcome the following: I am trying to create a chart whereby the source data is calculated from a formula. I do not want zero values to be plotted in the chart. I have tried the following, going to TOOLS, OPTIONS, CHARTS & then selecting 'plot empty cells as' = Not Plotted(leave gaps), but this does not overcome the problem. This function only seems to work when data is not related to a formula? I would be very grateful for any help that anybody can give Kind Regards Paul Paul: This is a very frequent question. You are half way to your answer. ...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

retrieving data from a table on a website
There is a internet page with chemical data : http://spreadsheets.google.com/pub?key=twQ35hFIq-y0N84xQ5l0ICQ&output=html It has chemicals in the first column and proerties in adjacent columns. Is there a way to have a userform lookup a chemical in a textbox from the websites 1st column and then populate other textboxes with the adjacent data from the table? Can anyone help? Thanks, Roger On Jan 18, 9:59=A0am, Roger on Excel <RogeronEx...@discussions.microsoft.com> wrote: > There is a internet page with chemical data : > > http://spreadsheets.googl...

how to retrieve archived emails
Hi, My company do not have an exchange server. We are using a 3rd party mail server which we subscribe from. I would like to know how can i retrieve and read my archived emails. Kindly advise. Thank you "Tiffany" <Tiffany@discussions.microsoft.com> wrote in message news:977BA64B-B730-4275-9AA1-86767AD242A5@microsoft.com... > Hi, > > My company do not have an exchange server. We are using a 3rd party mail > server which we subscribe from. I would like to know how can i retrieve > and > read my archived emails. > > Kindly advise. > > Thank y...

Options reset to values of most recently opened workbook
I've noticed a behavior in Excel 2000 SP-2 on Windows 2000 Professional SP-3 and I'm not sure if it is an error, by design, or correctable by some option or setting I'm missing When I open multiple workbooks I've found that the options settings under Tools/Options for the most recently opened workbook apply to ALL open workbooks. It might be best to explain by a couple of examples 1. I normally have the "Windows in Taskbar" option checked so I can view each of my open workbooks as a separate item in the taskbar. I have all of my workbooks set up this way. If I r...

Trend Line constants as Excel Cell Values
I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: > I want to put the constants of a 2 degree polynomial trend line into Excel &g...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

using VBA to retrieve values to ComboBox
i am trying to retrieve a range of data (the stock code of different phones) from worksheet("Inventory") based on the name of the branches of where the phones are stored. worksheet("Inventory") has branch (Column A) and stock code (Column B) i have 2 combobox. combobox1 contains values (name of branches) such as: 1-BS 2-EN 3-HG 4-JE 5-SP 6-TB 7-WS 8-YT combobox2 will have to retrieve values from the worksheet("Inventory") based on the value in combobox1. the values in combobox1 can be found in column 1 while the values i need for combobox...

Scatter Chart with non-numeric values on X axis
Hi, I'm trying to figure out how to create a scatter chart (graph) wit words for the x axis. I'm trying to setup a graph showing availabilit of servers, so for the Y axis I want to show a percentage (betwee 1-100%), and for the X axis, I want to show the name of the servic (mail, news, etc). However, it seems that with a scatter chart I ca only do numeric values on both axis. Is there any way around this? Thx -- penas ----------------------------------------------------------------------- penasm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...