Index match configured to ignore blank rows

[pbarr]

I have a large file with numerous “VLOOKUP” functions that I would like to change to “INDEX/MATCH” for efficiency. To do so, the INDEX/MATCH equation apparently must ignore blank rows in the data. Can this be done?

Here is my initial attempt which only works on the first item (row 4 (HRL)) but will not work on row 43 (UTX), apparently because of the intervening blank rows.

Formula in J4 - {=INDEX('Stock Input'!$X$4:$AA56,,4,MATCH(Alerts!B4,Alerts!$B$4:$B$56))}

Link to Spreadsheet in Dropbox – https://www.dropbox.com/s/i7fhvvs7nntrtt8/INDEX MATCH TEST v1.xlsm?dl=0

SHEETNAME "ALERTS"

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]HRL[/TD]
[TD]{=INDEX('Stock Input'!$X$4:$AA56,,4,MATCH(Alerts!B4,Alerts!$B$4:$B$56))}[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]43[/TD]
[TD]UTX[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]56[/TD]
[TD]CNMX[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

SHEETNAME "STOCK INPUT"

[TABLE="class: outer_border, width: 300"]
<tbody>[TR]
[TD]COL
#[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]HRL[/TD]
[TD]HORMEL[/TD]
[TD]29.35[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]17[/TD]
[TD]KR[/TD]
[TD]KROGER[/TD]
[TD]#DIV/0![/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]30[/TD]
[TD]NWL[/TD]
[TD]NEWELL[/TD]
[TD]#DIV/0![/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]43[/TD]
[TD]UTX[/TD]
[TD]UNITED TECH[/TD]
[TD]28.86[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help with a solution? Thanks in advance.

 

[Jonmo1]

=INDEX('Stock Input'!$AA$4:$AA56,MATCH(Alerts!B4,Alerts!$B$4:$B$56))

I'm pretty sure that should be

=INDEX('Stock Input'!$AA$4:$AA56,MATCH(Alerts!B4,'Stock Input'!$X$4:$X$56))

 

[pbarr]

Thanks for your reply Jonmo1. Unfortunately, that didn't work either. I have converted a number of my Vlookup formulas to Index,Match, but can't seem to get this one to work. The working formula is:
=vlookup(B6,'Stock Input'!$x$4:$ax$5591,10) where "B6" = Row Number, "x4:ax5591" = Database, and "10" is the Column Number of the potential answer. Any suggestions for converting this to index,match would be appreciated. Thanks in advance for your assistance.