IF/INDEX function searching for cell value contained within string of text

[coralasiy]

Hi,

i think this may be relatively easy but I have been searching high and low and cant find an answer,

I have an index formula which uses an if function in order to return a figures generated from two criteria derived from outputs in other cells.

Formula works fine when the a value in the range it searches exactly match the value in the cell however I cannot seem to find a way get it to search the range and match if cells contains the value.

Formula below with the problem section highlighted:

=IFERROR(INDEX(Table1[[#All],[Credit]],SMALL(IF((Table1[[#All],[Entity]]=Sheet1!C1)*[U][B](Table1[[#All],[Transaction]]=(Sheet1!D1)[/B][/U]),ROW(Table1[[#All],[Entity]]),""),2),1),0)

Cell D1 on Sheet1 has a value generated by a formula say for example 'Apples'
Transaction Column in Table 1 contains values which may contain Apples but within an string of text say 'Apples 213'

I have tried putting "*"&D1&"*" around the cell reference, ISnumber(search and Isnumber(match(...etc to no avail,

Is there a way I can amend my formula so that I am able to use this formula searching in a range of values which contain the reference value within strings of text?

Thank you for your help!

 

[DILIPandey]

HI Coralasiy,

Would be helpful if you can share a sample data here with your expected output manually entered. Thanks.

Regards,
DILIPandey

 

[coralasiy]

Hi Dilipandey,

A sample of my data would look like this:

Sheet 1 contains the following table of date:

Column C	Column D
Test 1	A
Test 1	B
Test 1	C

<tbody>
</tbody>

I then need to match those two variables to another table containing this data to generate an output from the column labelled 'Credit' below:

Entity	Transaction	Credit
Test 1	Transaction A 9987
Test 2	Transaction B 124	£100
Test 3	Transaction B 124	£200
Test 1	Transaction A 9987	£100

<tbody>
</tbody>

In this instance I want the formula match the two variables and pick up the first value matched but ignore blanks.

So Test 1, A would search for Test 1 then A in the table above and return a value of £100.

My formula above works fine if the values in the 'transaction' column match those in the table for sheet 1 however I cannot seem to edit it so that it searches for 'A' within the text string in the 'transaction' column.

Formula pasted below again for reference:

=IFERROR(INDEX(Table1[[#All],[Credit]],SMALL(IF((Table1[[#All],[Entity]]=Sheet1!C1)*[U][B](Table1[[#All],[Transaction]]=(Sheet1!D1)[/B][/U]),ROW(Table1[[#All],[Entity]]),""),2),1),0)

I have tried every way I can think of listed above but cannot find a solution, any help would be greatly appreciated,

Thanks
****** id="cke_pastebin" style="position: absolute; top: 307px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Transaction B 124

<tbody>
</tbody>

</body>

 

[DILIPandey]

Okay.. your first table in sheet 1 in column C & D.
Your second table in sheet 2 in column A to C.

Use below formula in cell E1 of sheet 1:-

=INDEX(Sheet2!$C$1:$C$5,MIN(ROWS((Sheet2!$A$1:$A$5=Sheet1!$C1)*(ISNUMBER(FIND(Sheet1!$D1,Sheet2!$B$1:$B$5)))*(Sheet2!$C$1:$C$5<>""))))

Entered as array formula which will fetch 100.

Regards,
DILIPandey

 

[coralasiy]

Worked perfectly, thank you very much for your help!

 

[DILIPandey]

You are welcome .... Cheers


Regards,
DILIPandey