+ Reply to Thread
Results 1 to 2 of 2

converting vlookup to index match across multiple sheets - fails if no match on 1st sheet

  1. #1
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Exclamation converting vlookup to index match across multiple sheets - fails if no match on 1st sheet

    Hello again folks. Just had some great help with another post so I'm hoping that this solution will be as simple and quick as the last from you excel experts.

    Basically I have the following VLOOKUP;

    =IF(C1757="Main",IFERROR(IF(H1757>"",VLOOKUP(H1757,INDIRECT("'"&INDEX('Ratios & User Notes'!$B$32:$I$32,MATCH(TRUE,COUNTIF(INDIRECT("'"&'Ratios & User Notes'!$B$32:$I$32&"'!C:C"),H1757)>0,0))&"'!C:I"),3,0),""),0),IF(C1757="Alt",IFERROR(IF(H1757>"",VLOOKUP(H1757,INDIRECT("'"&INDEX('Ratios & User Notes'!$C$32:$I$32,MATCH(TRUE,COUNTIF(INDIRECT("'"&'Ratios & User Notes'!$C$32:$I$32&"'!C:C"),H1757)>0,0))&"'!C:I"),3,0),""),0),""))

    Which I converted to an INDEX MATCH variant (as shown below), however if no match is found on the first shirt it just returns the first cell from the 1st sheet instead of moving to the next sheet. So I suspect my problem is with the INDEX part.

    =IF(C1757="Main",INDEX(INDIRECT("'"&'Ratios & User Notes'!$B$32:$I$32&"'!B:B"),MATCH(TRUE,COUNTIF(INDIRECT("'"&'Ratios & User Notes'!$B$32:$I$32&"'!C:C"),H1757)>0,0)),IF(C1757="Alt",INDEX(INDIRECT("'"&'Ratios & User Notes'!$C$32:$I$32&"'!B:B"),MATCH(TRUE,COUNTIF(INDIRECT("'"&'Ratios & User Notes'!$C$32:$I$32&"'!C:C"),H1757)>0,0)),""))

    Where have I gone wrong ? Do I needs some kind of TRUE COUNTIF method like with the MATCH part ?

    Thanks again folks!

    Nic

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,104

    Re: converting vlookup to index match across multiple sheets - fails if no match on 1st sh

    I find it difficult to visualise this big formulae. Any chance of posting your sheet so that we ca see it working (or not working, as the case may be!!).
    Glenn



+ 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 to check value across multiple sheets
    By HB07 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-03-2015, 03:57 AM
  2. [SOLVED] Vlookup/Index/Match on multiple sheets data
    By yabi0823 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-30-2014, 03:47 PM
  3. Replies: 2
    Last Post: 12-18-2014, 09:52 AM
  4. Vlookup (or index/match) with multiple criteria over multiple sheets
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 01:56 PM
  5. Replies: 13
    Last Post: 12-13-2012, 11:44 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