Nested INDEX MATCH with IF help, please.

[CS297]

Hi Guys, can anyone tell me what's wrong with the following construction, please? All three components work individually so I assume it's the way I have put them together that's the problem. In the below, only the first component works.

{=IF(M2="IMP",INDEX(SchASTM,MATCH(N2,IF(WTInchASTM=R2,NominalASTM),IF(M2="MET",INDEX(SchASTM,MATCH(N2,IF(WallASTM=R2,NominalASTM),IF(M2="SCH",R2)))))))}

Probably an easy fix for anyone who knows what they are doing with Excel formulae - unfortunately that's not me!!

Thanks so much in advance for your help.

 

[Gerald Higgins]

In what way does it not work exactly ?

What are you defining as the "first component" which does work ?

 

[CS297]

It returns a FALSE in the cell.
The following work as independent formulae:
{=IF(M2="IMP",INDEX(SchASTM,MATCH(N2,IF(WTInchASTM=R2,NominalASTM))))}
{=IF(M2="MET",INDEX(SchASTM,MATCH(N2,IF(WallASTM=R2,NominalASTM))))}
=IF(M2="SCH",R2)
I just need to put them all together into one forumula.

 

[njimack]

I think you need to explain what you're trying to do, with a small sample of your data. Both of your MATCH functions contain a nested IF function without the 3rd argument.

 

[AlanY]

Hi Guys, can anyone tell me what's wrong with the following construction, please? All three components work individually so I assume it's the way I have put them together that's the problem. In the below, only the first component works.

{=IF(M2="IMP",INDEX(SchASTM,MATCH(N2,IF(WTInchASTM=R2,NominalASTM),IF(M2="MET",INDEX(SchASTM,MATCH(N2,IF(WallASTM=R2,NominalASTM),IF(M2="SCH",R2)))))))}

Probably an easy fix for anyone who knows what they are doing with Excel formulae - unfortunately that's not me!!

Thanks so much in advance for your help.

in RED above, what if WTInchASTM not equal to R2?
etc

 

[CS297]

Am having trouble pasting images but the data is as follows:

My Table:
[TABLE="width: 488"]
<tbody>[TR]
[TD]DIMS[/TD]
[TD]NB
(mm)[/TD]
[TD]NB
(inch)[/TD]
[TD]OD
(inch)[/TD]
[TD]OD (mm)[/TD]
[TD]WT[/TD]
[TD]WT
(mm)[/TD]
[TD]WT
(inch)[/TD]
[TD]WT (SCH)[/TD]
[/TR]
[TR]
[TD]SCH[/TD]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]40[/TD]
[TD]7.11[/TD]
[TD]0.280[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

Source data:

[TABLE="width: 576"]
<tbody>[TR]
[TD]NPS mm[/TD]
[TD]Nom. Pipe Size[/TD]
[TD]OD inches[/TD]
[TD]OD mm[/TD]
[TD="colspan: 2"]Thickness
mm inches[/TD]
[TD]STD[/TD]
[TD]Sched[/TD]
[TD]Kg/m[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]7.11[/TD]
[TD]0.280[/TD]
[TD]STD[/TD]
[TD]40[/TD]
[TD]28.26[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]7.11[/TD]
[TD]0.280[/TD]
[TD][/TD]
[TD]STD[/TD]
[TD]28.26[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]10.97[/TD]
[TD]0.432[/TD]
[TD]XS[/TD]
[TD]80[/TD]
[TD]42.56[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]10.97[/TD]
[TD]0.432[/TD]
[TD][/TD]
[TD]XS[/TD]
[TD]42.56[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]14.27[/TD]
[TD]0.562[/TD]
[TD][/TD]
[TD]120[/TD]
[TD]54.21[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]18.26[/TD]
[TD]0.719[/TD]
[TD][/TD]
[TD]160[/TD]
[TD]67.57[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]21.95[/TD]
[TD]0.864[/TD]
[TD]XXS[/TD]
[TD]XXS[/TD]
[TD]79.22[/TD]
[/TR]
</tbody>[/TABLE]

Arrays:
A4:A174 = NominalASTM
B4:B174 = InchASTM
D4:D174 = ODASTM
E4:E174 = WallASTM
F4:F174 = WTInchASTM
H4:H174 = SchASTM
I4:I174 = KgmASTM

My customers order pipe thicknesses in 3 different ways, in Imperial, Metric and in Schedule. In column WT, I want to put the thickness however they have ordered it and then I want the next three columns to populate automatically with the three variants. To address this, I created column DIMS but I can't combine all the options into one formula. So basically, if I am working with Imperial, I put IMP under DIMS and enter, for example, 0.280 in the WT column and then I want the WT mm to update to 7.11, the inches to 0.280 and the Schedule to 40 (or STD).

In the above example, WTmm and WTinch work fine. Whether I enter IMP, MET or SCH, they are returning the correct values. The formulae in these cells are:
WTmm {=IF(M2="IMP",R2*25.4,IF(M2="MET",R2,IF(M2="SCH",INDEX(WallASTM,MATCH(N2,IF(SchASTM=R2,NominalASTM))))))}
WTinch {=IF(M2="IMP",R2,IF(M2="MET",R2/25.4,IF(M2="SCH",INDEX(WTInchASTM,MATCH(N2,IF(SchASTM=R2,NominalASTM))))))}

The issue with Sch is that the Metric or imperial value is not fixed. It differs based on the NB of the pipe - for example, STD in 6" is 7.11mm but in 8" it's 8.18mm. This is why I used an index/match formula.

 

[Gerald Higgins]

From post #3

It returns a FALSE in the cell.

This is because at least one of your IF statements (and possibly several of them) evaluate to FALSE, and you haven't specified in the IF statement what you want to do in that situation, as indicated in post #4 .

As your formula is quite complicated, it might not be obvious which part of the formula is the problem - try using Excel's Formula Evaluate tool to step through the formula one bit at a time.

 

[steve the fish]

I cant say as i know what that IF is trying to do inside the MATCH formulas but here is how you could nest those 3 formulas based on value of M2.

=IF(M2="IMP",INDEX(SchASTM,MATCH(N2,IF(WTInchASTM=R2,NominalASTM))),IF(M2="MET",INDEX(SchASTM,MATCH(N2,IF(WallASTM=R2,NominalASTM))),IF(M2="SCH",R2,"M2 doesn’t equal any")))

 

[CS297]

Thanks, Steve. WT SCH is now returning a Sch value but not the correct one. In the below, it's saying STD under Sch but it should say 80.

[TABLE="width: 523"]
<colgroup><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]DIMS[/TD]
[TD]NB
(mm)[/TD]
[TD]NB
(inch)[/TD]
[TD]OD
(inch)[/TD]
[TD]OD (mm)[/TD]
[TD]WT[/TD]
[TD]WT
(mm)[/TD]
[TD]WT
(inch)[/TD]
[TD]WT (SCH)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IMP[/TD]
[TD]150[/TD]
[TD]6"[/TD]
[TD]6.625[/TD]
[TD]168.3[/TD]
[TD]0.432[/TD]
[TD]10.97[/TD]
[TD]0.432[/TD]
[TD]STD[/TD]
[/TR]
</tbody>[/TABLE]

 

[CS297]

I think it's fixed. Slight tweak to the end and it works on all combos. Thanks so much, Steve.

{=IF(M2="IMP",INDEX(SchASTM,MATCH(N2,IF(WTInchASTM=R2,NominalASTM))),IF(M2="MET",INDEX(SchASTM,MATCH(N2,IF(WallASTM=R2,NominalASTM))),IF(M2="SCH",R2)))}