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))))))))))}

Hi-Tech 285w

13/04/2018

WaxMan

FALSE

<tbody>
</tbody>

 

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

Panel Type	Supplier		Panel Price
	WaxMan		#N/A
	Mid-Summer		#N/A
	WaxMan		FALSE

<tbody>
</tbody>

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))))))))))}

Hi-Tech 285w

13/04/2018

WaxMan

FALSE

<tbody>
</tbody>

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)))