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
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)),"")
=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
