# 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  Register To Reply

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!!).  Register To Reply