Ingnore blanks

[DHayes]

Good day,
I have the below function and it works 100%. I have now discovered that when there is a blank cell in the lookup the formula does not work. I would appreciate if any one could help me with the formula to ignore when it comes across a blank.

=INDEX(Sheet6!$E$10:$F$90,MATCH(LOOKUP(3,1/SEARCH(Sheet6!$E$10:$E$90,A2),Sheet6!$E$10:$E$90),Sheet6!$E$10:$E$90,0),2)

 

[AlanY]

try

=INDEX(Sheet6!$F$10:$F$90,MATCH(LOOKUP(3,1/SEARCH(Sheet6!$E$10:$E$20,A2),Sheet6!$E$10:$E$20),Sheet6!$E$10:$E$20,0))

 

[DHayes]

Hi AlanY,
Sorry I did try that one before still does not work.
I have given an example below. Sheet 1 is what I would like to achieve. But if there are spaces between the data (Sheet 6) it returns NA#.
Sheet 1

[TABLE="width: 416"]
<tbody>[TR]
[TD="align: left"]Item - 1 (AA)[/TD]
[TD="align: right"]2460[/TD]
[/TR]
[TR]
[TD="align: left"]Item - 2 (BB)[/TD]
[TD="align: right"]1334[/TD]
[/TR]
[TR]
[TD="align: left"]Item - 3 (CC)[/TD]
[TD="align: right"]1089[/TD]
[/TR]
[TR]
[TD="align: left"]Item - 4 (DD)[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: left"]Item - 5 (EE)[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 6[TABLE="width: 283"]
<tbody>[TR]
[TD="align: left"]Item - 1[/TD]
[TD="align: right"]2 460[/TD]
[/TR]
[TR]
[TD="align: left"]Item - 2[/TD]
[TD="align: right"]1 334[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Item - 3[/TD]
[TD="align: right"]1 089[/TD]
[/TR]
[TR]
[TD="align: left"]Item - 4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Item - 5[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

could you do the index/match like this instaed

=INDEX(Sheet6!$F$10:$F$22,MATCH(F1,Sheet6!$E$10:$E$22,0))

 

[DHayes]

Hi AlanY,
Sorry that does not work. The reason why there is a lookup in the array is because on sheet 1 there is additional information in the brackets and the formula ignores the bracket.
My problem is I can't get around the blank spaces. There is a reason for it as in the row where the blank is there is other data.

 

[FormR]

Hi, if your example data is representative - here is an alternative you could try:

=VLOOKUP(LEFT(A2,FIND(" (",A2&" (")-1),Sheet6!$E$10:$F$90,2,0)

 

[DHayes]

Hi FormR,
Thank you for your input it works. I have have adapted to index. I think I was over complicating it.
=INDEX(Sheet4!$E$2:$F$58,MATCH((LEFT(A2,FIND("(",A2&" (")-2)),Sheet4!$E$2:$E$58,0),2)

 

[AlanY]

Hi FormR,
Thank you for your input it works. I have have adapted to index. I think I was over complicating it.
=INDEX(Sheet4!$E$2:$F$58,MATCH((LEFT(A2,FIND("(",A2&" (")-2)),Sheet4!$E$2:$E$58,0),2)

only just realised what are you after, glad that you got it working

 

[FormR]

Hi, glad you got it working

I have have adapted to index.

Any particular reason for making that adaptation?

 

[DHayes]

Hi FormR,
Yes as I will be doing left and right index and matches.