formula help

pillr001

New Member
Joined
Sep 28, 2018
Messages
24
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 285w13/04/2018 WaxManFALSE

<tbody>
</tbody>
 

Some videos you may like

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

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
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
Joined
Sep 28, 2018
Messages
24
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
 

pillr001

New Member
Joined
Sep 28, 2018
Messages
24

ADVERTISEMENT

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

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

<tbody>
</tbody>

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

Ramballah

Board Regular
Joined
Sep 25, 2018
Messages
243
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Sep 28, 2018
Messages
24

ADVERTISEMENT

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
Joined
Sep 28, 2018
Messages
24
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 285w13/04/2018 WaxManFALSE

<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
Joined
Sep 25, 2018
Messages
243
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,993
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top