+ Reply to Thread
Results 1 to 10 of 10

how to fix vlookup formula across all the columns and rows to get data

  1. #1
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    how to fix vlookup formula across all the columns and rows to get data

    Hi to all,
    I have two sheets having data, in one data sheet having 1200 rows and 1000 columns data. I have lookup values in another sheet containing same 1200 rows as in first sheet. Now i want to get data for these lookup values (columns data) . Now i am using this vlookup formula
    =VLOOKUP(C2,'excelrange'!$D$5:$D$342,1,FALSE)
    Now i am getting data from 1 column, i want to get data from other columns (2 to 1000) by dragging my formula but i donot like to change column number always for every coulmn and i want to fix this for both rows and cloumns.
    Can any one help to solve my problem?
    any help would be appreciated
    Thanks in advance
    Regards,
    Genetist

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: how to fix vlookup formula across all the columns and rows to get data

    So if you want the same range of rows but want the columns letters to change try this:

    Please Login or Register  to view this content.
    But why are you looking up column 1 in the vlookup. Bascially you are looking for whatever is in C2 in column D (from row 4 to 342). If you find it, it returns the exact thing from cell C2. What is the point?

  3. #3
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: how to fix vlookup formula across all the columns and rows to get data

    Post a sample of your workbook. ALWAYS post a sample of your workbook.

  4. #4
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: how to fix vlookup formula across all the columns and rows to get data

    Hi sorry my late reply
    I have 1st column in both the sheets, now i want to extract data for 2nd sheet 1st column values from first sheet from row number 2 to 236 and from column B to T. i can use normal vlookup formula but i want drag same formula from column B to T and from row number 2 to 236 to get all my data in single attempt. Hope this explanation will help to solve my problem.
    Please see my attached file for better understanding.
    Thanking you very much
    Regards
    Genetist
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: how to fix vlookup formula across all the columns and rows to get data

    put this in cell B2 of sheet2
    =INDEX(Sheet1!$A$1:$T$237,MATCH(Sheet2!$A2,Sheet1!$A$1:$A$237,0),MATCH(Sheet2!B$1,Sheet1!$A$1:$T$1,0))

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: how to fix vlookup formula across all the columns and rows to get data

    genetist sample.xls

    Or see the attached file.

  7. #7
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: how to fix vlookup formula across all the columns and rows to get data

    Hi Mike Tron
    Thank you very much for your help but for some reasons this formula catching data for 23rd row and for column actually no exit for that column in 23rd row of my 1st sheet. do not know where went wrong

  8. #8
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: how to fix vlookup formula across all the columns and rows to get data

    I am not sure what you are saying but I think you migt be trying to say that yoru data on Sheet1 goes beyond colum T and beyond row 237?

    IF that is what you are saying then you will need to adjust the following to match thte actual array:

    =INDEX(Sheet1!$A$1:$T$237,MATCH(Sheet2!$A2,Sheet1!$A$1:$A$237,0),MATCH(Sheet2!B$1,Sheet1!$A$1:$T$1,0))

  9. #9
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: how to fix vlookup formula across all the columns and rows to get data

    I am sorry Mike TRON,
    in reality my sheet1 do not have any data in M column on 23rd row, but this formula bringing data to M column on 23rd row in my sheet2, thats what i mean, sorry i forget to write column M in my previous post.

  10. #10
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: how to fix vlookup formula across all the columns and rows to get data

    You lost me. When you post a sample data worksheet, it needs to resemble the actual worksheet or the entire exersize is not very valuable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. formula to combine data in rows and columns
    By crazytrain in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-26-2012, 05:58 PM
  2. Replies: 5
    Last Post: 03-01-2012, 05:08 AM
  3. Multiple ccolums/rows to get data from multiple columns/rows (vlookup)
    By Ramzes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2010, 05:35 AM
  4. return multiple columns/rows of data in formula?
    By darksoda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-01-2007, 09:19 AM
  5. [SOLVED] Macro to copy formula to all rows that contain data in columns A:C
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2006, 10:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1