Forum Discussion
How to validate multiple columns from a single duplicate column in 2 worksheets
Here you go. I used VLOOKUP. It looks as if EVERY code in the External sheet IS matched by one in the INTERNAL, so there's data to be brought over in every case. Is that in fact what you expected?
mathetes This is exactly what I need to do so thanks. Could you tell me the step by step process of how you did this please if you dont mind? Many thanks
The heart of this is the very useful function VLOOKUP. (I've read that this is and has been for a long time, something like the third most widely used of the functions within Excel.) There now are some other ways to accomplish the same thing, by the way. The basic ideas in VLOOKUP consist of the following:
- There's a "table" containing information that you want to retrieve. (In your case, that's the information on the "Internal" sheet.)
- There is some unique "index" or "identifier" for each row in that table. In your case, that's the "order ref no."
- And then there are columns of information, some specific one containing the information you want to retrieve. Those columns are offset from the identifier column by a specific number.
- There's the choice of "Exact Match" or not, represented by 0 or 1 respectively.
The VLOOKUP function gets written incorporating all those:
=VLOOKUP(#2,#1,#3,0)
So far as getting the green columns into your "External" sheet, that was simply a matter of inserting new columns and copying the headings. There needed to be a place to put the above formulas, and that's into those cells.
I hope this will help you understand how that worked. Feel free to come back with questions.
I do recommend that you look at the Excel help for more examples on VLOOKUP. Finally, not to overload you, there is another way to accomplish the same results, using a combination of INDEX and MATCH. I generally tend toward VLOOKUP, but have recently found the INDEX and MATCH functions offer some greater flexibility at times. You don't need them here, but they're worth researching.
- I will be honest this information doesnt help me to understand. If you were able to tell me a step by step on what you did with each section and the codes that would help me a lot. I find it hard to understand without having a step by step guide on the example. If you cannot do that no problem it would just help me understand the process a lot better. Many thanks
- Have you tried looking at the Excel help text on the VLOOKUP function? Or at an Excel reference text (i.e., a written book). If we were able to sit down together, side-by-side, maybe I could walk you through an example, but limited as we are to messaging back and forth, I can't imagine doing any better than what you'll find in the help text.
I will say this: the heart of a LOT of Excel functions is visualizing your data as a table, a set of rows of data arrayed in columns. Once you grasp that, the VLOOKUP becomes clear. All it's doing is looking up a given order number and then, once it finds it, going across the table the prescribed number of columns and retrieving the info and placing it in the cell where the formula resides.
But the table concept is at the heart, and until you grasp that, it will remain a mystery. Look at some simple examples, in the Help text...create some of your own.