+ Reply to Thread
Results 1 to 4 of 4

Multiple Rows & Columns Vlookup

  1. #1
    Registered User
    Join Date
    11-30-2011
    Location
    nagpur, India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Multiple Rows & Columns Vlookup

    Dear All,

    Since last three months I've tried my best to get the result, but I couldn't get. I have Three column heading "DATE, EMPLOYEE-NO, SHIFT" with more than 10000 rows. I want the results as each Employee Nos with 30/31 day's (Monthly) individual shift wise report.
    Sample data is enclosed.
    Please help me in this.
    Attached Files Attached Files
    Last edited by ashraf111; 11-28-2013 at 09:34 AM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Multiple Rows & Columns Vlookup

    For the amount of rows you have I think doing this with formulas would take a very long time to calculate - I would suggest looking at restructuring your data or something. Anyway, in your example a formula like:
    =IF(SUM(IF($A$1:$A$1777=TEXT(G$1,"0"),1,0)*IF($B$1:$B$1777=$F3,1,0)*IF($C$1:$C$1777)=$G2,1,0))>0,G$2,"")
    in G3 as an array formula (confirm with ctrl+shift+enter) and copied down and across would return your wanted result. If you combine this formulas to get your row/column headings dynamically though it will take ages to calculate for 10000rowsx31 columns.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,332

    Re: Multiple Rows & Columns Vlookup

    See the attachement
    Attached Files Attached Files
    Last edited by samba_ravi; 11-28-2013 at 07:00 AM.

  4. #4
    Registered User
    Join Date
    11-30-2011
    Location
    nagpur, India
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Multiple Rows & Columns Vlookup

    Hello Mr.nflsales... It's working and it SOLVE my problem. Thank You very much.

+ 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. VLookup or Index/match searching rows instead of columns or multiple criteria
    By Groovicles in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-16-2013, 05:00 PM
  2. Combining multiple rows from 2 columns into sorted columns depending on 1st columns value
    By Dexamphetamine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-11-2013, 10:00 AM
  3. Replies: 5
    Last Post: 03-01-2012, 05:08 AM
  4. Sum Multiple Rows and Columns by VLookUp
    By nomanahmedshah in forum Excel General
    Replies: 1
    Last Post: 03-07-2011, 06:46 AM
  5. 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

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