• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP, INDEX & MATCH for large lists

L

Lesley Keddy

New Member
I have two sheets of data in the same Workbook. One sheet is constantly being refreshed by cutting and pasting information from an external source. There can be up to 50 columns of data. In the 2nd sheet we are doing a series of VLOOKUPS to extract information from the 1st sheet against an account number.

Due to the number of columns this becomes very messy. I feel there must be a more efficient way of doing this - also I'd quite like to sometimes add extra columns. Is there a way I can extract data from the relevant column in the first sheet by using the column heading e.g. ACTIVE rather than referring to the 28th column?

EXAMPLE
=VLOOKUP(C3, Datafile!$AL$EK,28,0)

C3 = cell number where account number is typed
 
Hi Lesley,

Welcome to the chandoo forum.

You can use MATCH function to achieve this.

If you can upload a sample file, we can illustrate this for you.

Regards,
 
Thanks - I have been trying that but to no avail. Unfortunately I cannot upload the file because it is highly confidential and it is not easy for me to scramble the data in a way that it still makes sense. I will try and explain.

Sheet 1 contains data we have cut and paste from an external source
Sheet 2 contains some client numbers and names and we wish to add in extra information from sheet 1 that states which what type of portfolio, client name etc. There are just too many columns.

This is a sample formula entered into Sheet 2 which extracts the Client Name from the xx column in Sheet 1:

=VLOOKUP(C1,Sheet1!$A:$EK,13,0)

Although this works it relies on the name ALWAYS being in Col 13 plus I have 50 cols to extract random data from so it would be nice to use the column headings instead of column numbers.

Hope this makes sense?
 
Hi Lesley ,

Misra has already hinted that the MATCH function can be used.

The MATCH function returns the position of the looked up value in the data range. Suppose your header row is row #3 , which means your headers extend from A3 through EK3. Now suppose the 13th column has the header label Active.

Thus , your posted VLOOKUP formula can be rewritten as :

=VLOOKUP(C1,Sheet1!$A:$EK,MATCH("Active",Sheet1!$A3:$EK3,0),0)

The MATCH function will return 13 , and the VLOOKUP function will use this value to work as before.

Narayan
 
I have always had trouble understanding this formula. Assuming that you mean the cell number of the value to be looked up where you have put "Active" then I have used this formula but get #N/A
 
So I have:

(Actually the value is in C2 and the headings in Row 1 - sorry for any confusion I caused).

=VLOOKUP(C2,Sheet1!$A:$EK,MATCH(C2,Sheet1!$A1:$EK1,0),0)

This returns #N/A
 
I think you're just scratching the surface of the efficiency improvements you could possibly make here.

Here's some questions for you that will help us possibly make a radical improvement to the time it takes for the file to recalculate:

One sheet is constantly being refreshed by cutting and pasting information from an external source.
Click to expand...
  • Does the data in this sheet happen to be within an Excel Table?
  • Are there any formulas in the columns in that sheet (i.e. do you create calculated columns in that sheet, or when you cut data into it, are you bringing in 50 columns?)
  • How many rows are there in this sheet at the moment?
  • What version of Excel are you using?


In the 2nd sheet we are doing a series of VLOOKUPS to extract information from the 1st sheet against an account number.
Click to expand...
  • So are you looking up an item, and doing a number of different VLOOKUPS on that item to bring across data from other columns in Sheet 1 in relation to that item?
What I mean by that last bullet point, is that you say you use a formula like this in one column in Sheet2
=VLOOKUP(C1,Sheet1!$A:$EK,13,0)
...do you also use similar formulas in other columns of Sheet2? e.g.
=VLOOKUP(C1,Sheet1!$A:$EK,15,0)

If you do, then you should use the MATCH function to return the position of the thing you are looking for in Sheet1, and then use the INDEX function to pull the associated data for that item from the columns you need by reusing that MATCH result. Radical improvement in efficiency.

Have you considered whether using a PivotTable or the Advanced Filter could accomplish what you are trying to achieve?
 
ALso, I know you said that it's not easy to make up a sample file. But if you spent the time and did this, you would find that you get suggestions that are a great return on investment.
 
You must log in or register to reply here.
Back
Top