formula help

[pillr001]

Hello,

I can't figure out what's not working with this formula,

For example this is the formula below. When I try and look into the WaxMan sheet it is returning 'FALSE' but when I do the mid-summer one it returns the value i'm looking for, both mid-summer and waxman sheets are identical, so something is wrong with the formula string.. can someone please advise. I think the bit in red is where it's going wrong, basically IF i cannot find what i want in the mid-summer one i want it to look into the waxman sheet

{=IF(ISNUMBER(FIND("Mid-Summer",D9,1)),INDEX('Mid-Summer'!D2:D4,MATCH(1,IF(B9='Mid-Summer'!A2:A4,IF(C9>='Mid-Summer'!B2:B4,IF(C9<='Mid-Summer'!C2:C4,1),INDEX(WaxMan!D2:D4,MATCH(1,IF(B9=WaxMan!A2:A4,IF(C9>=WaxMan!B2:B4,IF(C9<=WaxMan!C2:C4,1,0))))))))))}

[TABLE="width: 411"]
<tbody>[TR]
[TD]Hi-Tech 285w[/TD]
[TD="align: right"]13/04/2018[/TD]
[TD] WaxMan[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Try this,

=IF(ISNUMBER(FIND("Mid-Summer",D9,1)),INDEX('Mid-Summer'!D2:D4,MATCH(1,IF(B9='Mid-Summer'!A2:A4,IF(C9>='Mid-Summer'!B2:B4,IF(C9<='Mid-Summer'!C2:C4,1),INDEX(WaxMan!D2:D4,MATCH(1,IF(B9=WaxMan!A2:A4,IF(C9>=WaxMan!B2:B4,IF(C9<=WaxMan!C2:C4,1))),0)))),0)))

 

[pillr001]

it does not seem to be working, still return 'false' the mid-summer is working and the sheet is exactly the same as the waxman sheet.. i'm really confused

 

[admiral100]

What does this formula return ?
=ISNUMBER(FIND("Mid-Summer",D9,1))

 

[pillr001]

What does this formula return ?
=ISNUMBER(FIND("Mid-Summer",D9,1))

[TABLE="width: 411"]
<tbody>[TR]
[TD]Panel Type [/TD]
[TD]Supplier [/TD]
[TD][/TD]
[TD]Panel Price [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WaxMan[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mid-Summer[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WaxMan[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

This looks into the supplier column, then determines what supplier sheet to look into.

 

[Ramballah]

i might be wrong but is your sheet named Wax-Man like in the supplier column? Because in your formula there is only WaxMan but it is Mid-Summer and ur sheet looks to be named Mid-Summer.

 

[pillr001]

i might be wrong but is your sheet named Wax-Man like in the supplier column? Because in your formula there is only WaxMan but it is Mid-Summer and ur sheet looks to be named Mid-Summer.

Oh no apologies, it's waxman in the spreadsheet.

 

[pillr001]

Hello,

I can't figure out what's not working with this formula,

For example this is the formula below. When I try and look into the WaxMan sheet it is returning 'FALSE' but when I do the mid-summer one it returns the value i'm looking for, both mid-summer and waxman sheets are identical, so something is wrong with the formula string.. can someone please advise. I think the bit in red is where it's going wrong, basically IF i cannot find what i want in the mid-summer one i want it to look into the waxman sheet

{=IF(ISNUMBER(FIND("Mid-Summer",D9,1)),INDEX('Mid-Summer'!D2:D4,MATCH(1,IF(B9='Mid-Summer'!A2:A4,IF(C9>='Mid-Summer'!B2:B4,IF(C9<='Mid-Summer'!C2:C4,1),INDEX(WaxMan!D2:D4,MATCH(1,IF(B9=WaxMan!A2:A4,IF(C9>=WaxMan!B2:B4,IF(C9<=WaxMan!C2:C4,1,0))))))))))}

[TABLE="width: 411"]
<tbody>[TR]
[TD]Hi-Tech 285w[/TD]
[TD="align: right"]13/04/2018[/TD]
[TD] WaxMan[/TD]
[TD="align: center"]FALSE
[/TD]
[/TR]
</tbody>[/TABLE]

So does anyone know what the problem is, i could upload to google sheets and see if you could work out whats wrong?

 

[Ramballah]

oh i see it. you got '' for mid-summer but not for Waxman. so it should be INDEX('Waxman'!D2:D4,MATCH etc. For all the mid summers u used '' but for not one waxman u did.

 

[steve the fish]

Its this isnt it:

=IF(ISNUMBER(FIND("Mid-Summer",D9)),INDEX('Mid-Summer'!D2:D4,MATCH(1,IF(B9='Mid-Summer'!A2:A4,IF(C9>='Mid-Summer'!B2:B4,IF(C9<='Mid-Summer'!C2:C4,1))),0)),INDEX(WaxMan!D2:D4,MATCH(1,IF(B9=WaxMan!A2:A4,IF(C9>=WaxMan!B2:B4,IF(C9<=WaxMan!C2:C4,1))),0)))