VLOOKUP: Retrieving Values

[mellowe]

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 D725 = 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

 

[L. Howard Kittle]

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

 

[Niek Otten]

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

 

[mellowe]

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?

 

[Roger Govier]

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]

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!

 

[mellowe]

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!

 

[Roger Govier]

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