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

1. ## 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. ## 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!!).

##### Users Browsing this Thread

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

#### 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