CODEWITHSUNDEEP
excelexcel-formula
hsquared
hsquared

Reputation: 359

Excel Index Match - Only return value if begins with certain letter

I have an index match formula however I need it to only display if the result begins with a certain character.

The formula I currently have is: 

 =INDEX('1'!E10:E205,MATCH(B36,'1'!G10:G205, 0))

However when returning the value (in E10:E205) column I need it only to display if it begins with an 'x'.

I just can't figure out where to go with this so any help would be appreciated! Thanks

Upvotes: 0

Views: 3190

Answers (2)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

=INDEX('1'!E10:E205,MATCH(B36,'1'!G10:G205, 0))

Okay so the row number returned at the moment is when '1'!G matches B36. You want to add another logical on the row number, offset to the start of the INDEX (indexed position). There are a couple of ways to do this:

First you can build a string of the two columns together for MATCH to evaluate and use wildcards:

=INDEX('1'!E10:E205,MATCH("x*"&B36,'1'!E10:E205&'1'!G10:G205, 0))

The * wildcard will allow for any number of any character so you will get what you are looking for.

Another way would be to use SMALL(IF()) to build an array of index postions and return the nth smallest:

=INDEX('1'!E10:E205,SMALL(IF(LEFT('1'!E10:E205,1)="x",IF('1'!G10:G205=B36,ROW('1'!G10:G205)-9)),ROW(10:10))) - This is an array formula; Ctrl + Shift + Enter While still in the formula bar

Upvotes: 0

muzzyq
muzzyq

Reputation: 894

Is this what you mean?

=IF(LEFT(INDEX('1'!E10:E205,MATCH(B36,'1'!G10:G205,0)),1)="x",INDEX('1'!E10:E205,MATCH(B36,'1'!G10:G205,0)),"")

Upvotes: 2

Related Questions