# formula help

#### pillr001

##### New Member
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>

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

##### Well-known Member
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

##### New Member
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

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

#### pillr001

##### New Member

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.

Last edited:

#### Ramballah

##### Board Regular
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

##### New Member

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

##### New Member
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

##### Board Regular
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

##### Well-known Member
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)))

Replies
2
Views
183
Replies
1
Views
56
Replies
6
Views
67
Replies
4
Views
51
Replies
9
Views
79