Bulk Import

[Mark H]

"Macros are easy in Excel - just make a simple sample, record it and then edit it."

Then spend the next two years persuading Vista that, yes, you are a grown up, it is your PC, you are the master of this situation, and yes, you would really like to run your own spreadsheet which contains macros that you wrote and would quite like to use, thank you very much.

[kessock]

Macros

Think I will have to continue the old manual input way, this is not at easy at it should be.

[dallen]

you want to use VLOOKUP

format would be:

=VLOOKUP(A1, Sheet2!A:E, 5, FALSE)

this will lookup the value of cell A1 in the current sheet in the range A:E in Sheet2 and return the value in column 5 (E) and only return an exact match to A1.

Have a read up on it - it should save you stacks of time without having to learn anything too complicated...

[kessock]

Reply

Thanks for that.
Will this only return the value of say E if A matches in both sheets?
Thanks.
Mark

[Duncan Rounding]

It's not quite so simple - best is to make a simple sample spreadsheet and have a play with the function.

You need to set ranges relevant to the lookup. Your match data may also need to be in alphabetical order as I think it reports first match then moves on - it may not start at the beginning for each product search (hope that makes sense).

[leehack]

It is a great function this one, but it does have a few pitfalls as Duncan says. Best thing on a new function is to use the wizard as it will explain each part and what you are actually doing. You can specify to find an exact match though, so don't worry about that part. When comparing 2 things on 2 different worksheets, you will often find it is better to first get the rows next to each other in the same worksheet, then run your formula and you can instantly see where it highlights and act accordingly. You can even get a 2nd formula to put the correct price into a third column and that is the column you then use mapped to the product reference.

[kessock]

Replies

Thanks guys.
I am off to buy a dummies guide for formulas.
I have 2 versions of excel and neither have the wizard, not that is easy to find anyway.
Cheers.
Mark

[leehack]

They will both have the wizard, unless they are seriously old versions. Click the fx on the formula bar.

[kessock]

reply

Hi Lee,
Thanks for that I will take a look.
cheers.
mark

[kessock]

Look up

Hi All,
Just had 5 minutes to take a look.
Can you please confirm that the sheet the formula is searching needs to be in alphabetical/numerical order i.e the cell I am searching?
Thanks again.
Mark

[leehack]

I've never found it necessary, if you read the instructions on the wizard (the last field in particular) i think it will tell you what you need to do to get an exact match. Having said that, having the sheet in some kind of order is probably a good idea anyway, so just do it on the main lookup field, which would usually be either the product ref or the product name and use those fields to do the lookup, making sure they are in the far left column.

[kessock]

Reply

Thanks for the fast response.
I will give it a go.
Mark

[dallen]

Just to give you a bit more of a breakdown (but you'll probably need to play with it and read some more on it)

=vlookup(A1, 'Sheet2'!A:B, 2, FALSE)

Means it will search for the value of cell A1 in the current sheet.
It will search in Sheet2
It will search for the value in the first column of the range specified (Columns A and B in this case). You will ALWAYS need to specify a range where the value you are searching for is in the FIRST column. ALWAYS!
It will then return the value from that row in the column number specified (column 2 in this instance). If you told it to search in the range G:L column G would be column 1, column L would be column 6.
It will need an exact match to the specified search criteria - that is the FALSE part (but not all of it - it behaves differently depending on what values you're looking up). Set it to FALSE and if it can't find an exact match it will return #N/A which you can then error trap by using IF(ISNA())

/edit as far as ascending/descending order, if you're looking for a unique identifier (i.e. productreference) and you set the last part to FALSE, then it doesn't need to be in any order. The ascending order thing is for if you're looking up a numerical value in a list and there are duplicates or you want to find the closest match. But as you're looking up a unique identifier (productreference) you need not worry about this as long as you set it to FALSE.

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.

If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

[kessock]

stuck

Hi again,
Have finally got 5 minutes to try and get this sorted.
If my reference is coloumn A but the price I am looking for is say in sheet 1 column H and the lookup reference is Sheet 1 column B but on a different spreadsheet altogether what do I need to input on the =vlookup(A1 etc as I am baffled.
Thanks.
mark

[leehack]

Use the wizard it will guide you and instruct you on what each part of the formula requires and how to set things up.