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

Item - 1 (AA)	2460
Item - 2 (BB)	1334
Item - 3 (CC)	1089
Item - 4 (DD)	2
Item - 5 (EE)	0

<tbody>
</tbody>

Sheet 6

Item - 1	2 460
Item - 2	1 334
Item - 3	1 089
Item - 4	2
Item - 5	0

<tbody>
</tbody>

 

[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.