For question 1, you could do something fancy using INDEX and OFFSET, but I
wouldn't bother for this: just as easy to use a VLOOKUP in each cell.
It would be slow if you wanted to look at thousands of rows and hundreds of
columns, but for this Excel will do it in a blink of an eye. Remember, it
doesn't matter how hard the computer has to work, but it does matter how
hard YOU do. So let's just keep it simple and make Excel do the work
Let's assume that your "Tracking Spreadsheet" is "Sheet 2" and your
"Delivery" spreadsheet is "Sheet 1".
Your Delivery is in cells A1 to F1 and down...
To make this easier to code, I simply assigned a "Name" to the whole of the
Delivery table (Select all the cells in the delivery sheet and Type a Name
in the Name box. I chose "Delivery"; I love to see what I am doing
expressed in plain English
In Column A of the Tracking sheet, you add the names of the magazines. If
the names are not unique, choose something that is unique, like their
publication number: this thing will give wrong results if there are two
names the same.
To get VLOOKUP to work, the column you are looking IN in the Delivery sheet
MUST be the left-most column. It does not have to be Column A, but it must
be the left-most column in the table that you name. If that's not the case,
you may still be able to get it to work, but then you will need to use INDEX
and OFFEST and the formula becomes really convoluted.
Now, for Column B in the Tracking sheet, the Formula becomes:
=VLOOKUP($A1,Delivery,2,FALSE)
That will look in the left column of the "Delivery" table for the name that
appears in cell A1, and return to cell B1 of the Tracking sheet whatever
appears in the second column of the Delivery table: the one to the right of
where it found the name. In this case, that's the delivered number.
Because I used the name "Delivery" for the entire delivery table, and did
not do anything special, I do not have to bother telling Excel which sheet I
want it to look on. Let Excel do the work...
Notice that I prefixed "A1" with a $ sign. That's because I know that I
will want to "Fill Down" later, and I don't want Excel changing the column
number, only the Row number.
The Value "FALSE" at the end simply tells Excel "If you don't find it,
return "N/A". Otherwise, Excel will return the next highest name
alphabetically in the list, which will turn your results to nonsense because
it won't be looking at the delivery for the correct magazine. This also
means that you do not have to have the names in alphabetic order, in either
sheet: Excel will look at the whole list.
Now, you're right: What happens if there was no delivery of this magazine
today? You WOULD get a #N/A. We need to KNOW if we didn't find it, but we
don't want to SEE it. So let's deal with that.
=IF(ISNA(VLOOKUP($A1,Delivery,2,FALSE))=TRUE,""
This uses the IF statement to insert a conditional. Then it uses the
built-in "ISNA" function to decide whether what we get is a "N/A" or not.
And as you can see, we simply repeat the formula. Effectively we're saying
to Excel, "First try the look-up: if you find nothing, set the cell to
blank; otherwise, go back and get the content again and put it in the cell."
The whole formula looks like this:
=IF(ISNA(VLOOKUP($A1,Delivery,2,FALSE))=TRUE,"",VLOOKUP($A1,Delivery,2,FALSE
))
It reads "If ISNA{what our lookup formula found} returns True, then set the
cell to " ", otherwise, set the cell to whatever the lookup formula returns.
Now, copy that formula into the Tracking sheet on Columns C, D, E, and F
In Cell C1, set the returned column number to "3" in each lookup:
=IF(ISNA(VLOOKUP($A1,Delivery,3,FALSE))=TRUE,"",VLOOKUP($A1,Delivery,3,FALSE
))
In Cell D1, set it to 4, in E1 set it to 5, etc. The numbers do not have to
be in order, you can swap the columns around in this step. And they do not
have to be contiguous: if you don't want a column, don't retrieve it.
Now, select Cells B1 to F1, grab the fill handle and drag it down the full
list of titles...
All done! It takes a lot longer to describe it than it does to do it
Cheers
Hello and thanks for the quick reply . I 've seen some examples and tips about
the H-V- LOOKUP and i need some more information. If you could answer this
questions the function would be more clear for me .
1. How will i be able to retrieve from the new sheet not just one but
multiple data ( price , date , issue number ) based on the search of the
magazine 's name
2. Given the fact that i receive more or less 1.000 titles of magazines and
newspapers and their names will appear minimum 1 time (monthly) and maximum 4
times (weekly) and i don 't get the exactly same amount of titles everyday (
today their may be 25 titles and tomorrow 74 the next day 56 and so on ) wont
it be a #N/A error message because my reference will be to an empty cell maybe
up to 26 days a month ;
Thanks again for your help !
--
This email is my business email -- Please do not email me about forum
matters unless you intend to pay!
John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:
[email protected]