Nestling: The specified formula cannot be entered because it uses more than 64 levels of nestling

Beunite

New Member
Joined
Apr 22, 2016
Messages
4
Hi there,

I'm new to the community. Please help me to follow the right procedures.

I have a problem with nestling and I need your help.

Here's the formula, it's a simple ifand formula. The formula works like a beauty until the last city (Yangon). Then the error message pops up.

The specified formula cannot be entered because it uses more than 64 levels of nestling

It consists of the cities, Bangkok, Phuket, KohSamui, etc. and the duration half-day, full-day, two-day, etc. so it pretty much repeats itself from the beginning up until the with all combinations of cities and duration. I will have more cities to add in the future, up to 50.

I hope it's not too long and doesn't cause confusion:

Thank you for helping me to crack this nut.

Kind regards

This is the formula that works up until Yangon:

=IF(AND($C4="Bangkok",$A4="shorter than half-day"),Accommodation!$B$2,IF(AND($C4="Bangkok",$A4="half-day"),Accommodation!$B$3,IF(AND($C4="Bangkok",$A4="full-day"),Accommodation!$B$4,IF(AND($C4="Bangkok",$A4="two-day"),Accommodation!$B$5,IF(AND($C4="Bangkok",$A4="longer than two days"),Accommodation!$B$6,IF(AND($C4="Phuket",$A4="shorter than half-day"),Accommodation!$C$2,IF(AND($C4="Phuket",$A4="half-day"),Accommodation!$C$3,IF(AND($C4="Phuket",$A4="full-day"),Accommodation!$C4,IF(AND($C4="Phuket",$A4="two-day"),Accommodation!$C$5,IF(AND($C4="Phuket",$A4="longer than two days"),Accommodation!$C$6,IF(AND($C4="Koh Samui",$A4="shorter than half-day"),Accommodation!$D$2,IF(AND($C4="Koh Samui",$A4="half-day"),Accommodation!$D$3,IF(AND($C4="Koh Samui",$A4="full-day"),Accommodation!$D$4,IF(AND($C4="Koh Samui",$A4="two-day"),Accommodation!$D$5,IF(AND($C4="Koh Samui",$A4="longer than two days"),Accommodation!$D$6,IF(AND($C4="Hua Hin",$A4="shorter than half-day"),Accommodation!$E$2,IF(AND($C4="Hua Hin",$A4="half-day"),Accommodation!$E$3,IF(AND($C4="Hua Hin",$A4="full-day"),Accommodation!$E$4,IF(AND($C4="Hua Hin",$A4="two-day"),Accommodation!$E$5,IF(AND($C4="Hua Hin",$A4="longer than two days"),Accommodation!$E$6,IF(AND($C4="Pattaya",$A4="shorter than half-day"),Accommodation!$F$2,IF(AND($C4="Pattaya",$A4="half-day"),Accommodation!$F$3,IF(AND($C4="Pattaya",$A4="full-day"),Accommodation!$F$4,IF(AND($C4="Pattaya",$A4="two-day"),Accommodation!$F$5,IF(AND($C4="Pattaya",$A4="longer than two days"),Accommodation!$F$6,IF(AND($C4="Chiang Mai",$A4="shorter than half-day"),Accommodation!$G$2,IF(AND($C4="Chiang Mai",$A4="half-day"),Accommodation!$G$3,IF(AND($C4="Chiang Mai",$A4="full-day"),Accommodation!$G$4,IF(AND($C4="Chiang Mai",$A4="two-day"),Accommodation!$G$5,IF(AND($C4="Chiang Mai",$A4="longer than two days"),Accommodation!$G$6, IF(AND($C4="Singapore",$A4="shorter than half-day"),Accommodation!$H$2,IF(AND($C4="Singapore",$A4="half-day"),Accommodation!$H$3,IF(AND($C4="Singapore",$A4="full-day"),Accommodation!$H$4,IF(AND($C4="Singapore",$A4="two-day"),Accommodation!$H$5,IF(AND($C4="Singapore",$A4="longer than two days"),Accommodation!$H$6, IF(AND($C4="Hong Kong",$A4="shorter than half-day"),Accommodation!$I$2,IF(AND($C4="Hong Kong",$A4="half-day"),Accommodation!$I$3,IF(AND($C4="Hong Kong",$A4="full-day"),Accommodation!$I$4,IF(AND($C4="Hong Kong",$A4="two-day"),Accommodation!$I$5,IF(AND($C4="Hong Kong",$A4="longer than two days"),Accommodation!$I$6,IF(AND($C4="Kuala Lumpur",$A4="shorter than half-day"),Accommodation!$J$2,IF(AND($C4="Kuala Lumpur",$A4="half-day"),Accommodation!$J$3,IF(AND($C4="Kuala Lumpur",$A4="full-day"),Accommodation!$J$4,IF(AND($C4="Kuala Lumpur",$A4="two-day"),Accommodation!$J$5,IF(AND($C4="Kuala Lumpur",$A4="longer than two days"),Accommodation!$J$6,IF(AND($C4="Jakarta",$A4="shorter than half-day"),Accommodation!$K$2,IF(AND($C4="Jakarta",$A4="half-day"),Accommodation!$K$3,IF(AND($C4="Jakarta",$A4="full-day"),Accommodation!$K$4,IF(AND($C4="Jakarta",$A4="two-day"),Accommodation!$K$5,IF(AND($C4="Jakarta",$A4="longer than two days"),Accommodation!$K$6,IF(AND($C4="Siem Reap",$A4="shorter than half-day"),Accommodation!$L$2,IF(AND($C4="Siem Reap",$A4="half-day"),Accommodation!$L$3,IF(AND($C4="Siem Reap",$A4="full-day"),Accommodation!$L$4,IF(AND($C4="Siem Reap",$A4="two-day"),Accommodation!$L$5,IF(AND($C4="Siem Reap",$A4="longer than two days"),Accommodation!$L$6,IF(AND($C4="Yangon",$A4="shorter than half-day"),Accommodation!$M$2,IF(AND($C4="Yangon",$A4="half-day"),Accommodation!$M$3, IF(AND($C4="Yangon",$A4="full-day"),Accommodation!$M$4,IF(AND($C4="Yangon",$A4="two-day"),Accommodation!$M$5,IF(AND($C4="Yangon",$A4="longer than two days"),Accommodation!$M$6 ))))))))))))))))))))))))))))))))))))))))))))))))))))))) )))))

This is the last part of the formula that doesn't work when added to the previous one:

,IF(AND($C4="Colombo",$A4="shorter than half-day"),'ACCOMMODATION'!$N$2,IF(AND($C4="Colombo",$A4="half-day"),'ACCOMMODATION'!$N$3,IF(AND($C4="Colombo",$A4="full-day"),'ACCOMMODATION'!$N$4,IF(AND($C4="Colombo",$A4="two-day"),'ACCOMMODATION'!$N$5,IF(AND($C4="Colombo",$A4="longer than two days"),'ACCOMMODATION'!$N$6 )))))


This is the entire formula that unfortunately doesn't work:

=IF(AND($C4="Bangkok",$A4="shorter than half-day"),Accommodation!$B$2,IF(AND($C4="Bangkok",$A4="half-day"),Accommodation!$B$3,IF(AND($C4="Bangkok",$A4="full-day"),Accommodation!$B$4,IF(AND($C4="Bangkok",$A4="two-day"),Accommodation!$B$5,IF(AND($C4="Bangkok",$A4="longer than two days"),Accommodation!$B$6,IF(AND($C4="Phuket",$A4="shorter than half-day"),Accommodation!$C$2,IF(AND($C4="Phuket",$A4="half-day"),Accommodation!$C$3,IF(AND($C4="Phuket",$A4="full-day"),Accommodation!$C4,IF(AND($C4="Phuket",$A4="two-day"),Accommodation!$C$5,IF(AND($C4="Phuket",$A4="longer than two days"),Accommodation!$C$6,IF(AND($C4="Koh Samui",$A4="shorter than half-day"),Accommodation!$D$2,IF(AND($C4="Koh Samui",$A4="half-day"),Accommodation!$D$3,IF(AND($C4="Koh Samui",$A4="full-day"),Accommodation!$D$4,IF(AND($C4="Koh Samui",$A4="two-day"),Accommodation!$D$5,IF(AND($C4="Koh Samui",$A4="longer than two days"),Accommodation!$D$6,IF(AND($C4="Hua Hin",$A4="shorter than half-day"),Accommodation!$E$2,IF(AND($C4="Hua Hin",$A4="half-day"),Accommodation!$E$3,IF(AND($C4="Hua Hin",$A4="full-day"),Accommodation!$E$4,IF(AND($C4="Hua Hin",$A4="two-day"),Accommodation!$E$5,IF(AND($C4="Hua Hin",$A4="longer than two days"),Accommodation!$E$6,IF(AND($C4="Pattaya",$A4="shorter than half-day"),Accommodation!$F$2,IF(AND($C4="Pattaya",$A4="half-day"),Accommodation!$F$3,IF(AND($C4="Pattaya",$A4="full-day"),Accommodation!$F$4,IF(AND($C4="Pattaya",$A4="two-day"),Accommodation!$F$5,IF(AND($C4="Pattaya",$A4="longer than two days"),Accommodation!$F$6,IF(AND($C4="Chiang Mai",$A4="shorter than half-day"),Accommodation!$G$2,IF(AND($C4="Chiang Mai",$A4="half-day"),Accommodation!$G$3,IF(AND($C4="Chiang Mai",$A4="full-day"),Accommodation!$G$4,IF(AND($C4="Chiang Mai",$A4="two-day"),Accommodation!$G$5,IF(AND($C4="Chiang Mai",$A4="longer than two days"),Accommodation!$G$6, IF(AND($C4="Singapore",$A4="shorter than half-day"),Accommodation!$H$2,IF(AND($C4="Singapore",$A4="half-day"),Accommodation!$H$3,IF(AND($C4="Singapore",$A4="full-day"),Accommodation!$H$4,IF(AND($C4="Singapore",$A4="two-day"),Accommodation!$H$5,IF(AND($C4="Singapore",$A4="longer than two days"),Accommodation!$H$6, IF(AND($C4="Hong Kong",$A4="shorter than half-day"),Accommodation!$I$2,IF(AND($C4="Hong Kong",$A4="half-day"),Accommodation!$I$3,IF(AND($C4="Hong Kong",$A4="full-day"),Accommodation!$I$4,IF(AND($C4="Hong Kong",$A4="two-day"),Accommodation!$I$5,IF(AND($C4="Hong Kong",$A4="longer than two days"),Accommodation!$I$6,IF(AND($C4="Kuala Lumpur",$A4="shorter than half-day"),Accommodation!$J$2,IF(AND($C4="Kuala Lumpur",$A4="half-day"),Accommodation!$J$3,IF(AND($C4="Kuala Lumpur",$A4="full-day"),Accommodation!$J$4,IF(AND($C4="Kuala Lumpur",$A4="two-day"),Accommodation!$J$5,IF(AND($C4="Kuala Lumpur",$A4="longer than two days"),Accommodation!$J$6,IF(AND($C4="Jakarta",$A4="shorter than half-day"),Accommodation!$K$2,IF(AND($C4="Jakarta",$A4="half-day"),Accommodation!$K$3,IF(AND($C4="Jakarta",$A4="full-day"),Accommodation!$K$4,IF(AND($C4="Jakarta",$A4="two-day"),Accommodation!$K$5,IF(AND($C4="Jakarta",$A4="longer than two days"),Accommodation!$K$6,IF(AND($C4="Siem Reap",$A4="shorter than half-day"),Accommodation!$L$2,IF(AND($C4="Siem Reap",$A4="half-day"),Accommodation!$L$3,IF(AND($C4="Siem Reap",$A4="full-day"),Accommodation!$L$4,IF(AND($C4="Siem Reap",$A4="two-day"),Accommodation!$L$5,IF(AND($C4="Siem Reap",$A4="longer than two days"),Accommodation!$L$6,IF(AND($C4="Yangon",$A4="shorter than half-day"),Accommodation!$M$2,IF(AND($C4="Yangon",$A4="half-day"),Accommodation!$M$3, IF(AND($C4="Yangon",$A4="full-day"),Accommodation!$M$4,IF(AND($C4="Yangon",$A4="two-day"),Accommodation!$M$5,IF(AND($C4="Yangon",$A4="longer than two days"),Accommodation!$M$6 ,IF(AND($C4="Colombo",$A4="shorter than half-day"),'ACCOMMODATION'!$N$2,IF(AND($C4="Colombo",$A4="half-day"),'ACCOMMODATION'!$N$3,IF(AND($C4="Colombo",$A4="full-day"),'ACCOMMODATION'!$N$4,IF(AND($C4="Colombo",$A4="two-day"),'ACCOMMODATION'!$N$5,IF(AND($C4="Colombo",$A4="longer than two days"),'ACCOMMODATION'!$N$6 ))))))))))))))))))))))))))))))))))))))))))))))))))))))) ))))) )))))
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,570
You really shouldn't be trying to use IF tests for that. Apart from anything else, they're a maintenance nightmare. And, as you have discovered, you can only nest them so far...

What you should do is create a lookup table, which would then allow you to use one of Excel's LOOKUP functions or an INDEX/MATCH function combination.
 

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
512
Office Version
  1. 365
Platform
  1. Windows
Looks like you already have a table in "Accommodation" sheet.

Make your table like this in "Accommodation" Sheet:

Note: The numbers generated are random numbers as I don't know the result of matching criteria.

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">shorter then half-day</td><td style="font-weight: bold;text-align: center;;">half-day</td><td style="font-weight: bold;text-align: center;;">full-day</td><td style="font-weight: bold;text-align: center;;">two-day</td><td style="font-weight: bold;text-align: center;;">longer then two day</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Bangkok</td><td style="text-align: right;;">292</td><td style="text-align: right;;">128</td><td style="text-align: right;;">157</td><td style="text-align: right;;">485</td><td style="text-align: right;;">214</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;;">phuket</td><td style="text-align: right;;">244</td><td style="text-align: right;;">263</td><td style="text-align: right;;">165</td><td style="text-align: right;;">386</td><td style="text-align: right;;">258</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;;">Koh Samui</td><td style="text-align: right;;">320</td><td style="text-align: right;;">445</td><td style="text-align: right;;">191</td><td style="text-align: right;;">431</td><td style="text-align: right;;">407</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;;">Hua Hin</td><td style="text-align: right;;">228</td><td style="text-align: right;;">491</td><td style="text-align: right;;">321</td><td style="text-align: right;;">141</td><td style="text-align: right;;">424</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;;">Pattaya</td><td style="text-align: right;;">177</td><td style="text-align: right;;">222</td><td style="text-align: right;;">204</td><td style="text-align: right;;">322</td><td style="text-align: right;;">203</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;;">Chiang Mai</td><td style="text-align: right;;">193</td><td style="text-align: right;;">186</td><td style="text-align: right;;">125</td><td style="text-align: right;;">169</td><td style="text-align: right;;">139</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;;">Singapore</td><td style="text-align: right;;">304</td><td style="text-align: right;;">398</td><td style="text-align: right;;">261</td><td style="text-align: right;;">269</td><td style="text-align: right;;">221</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;;">Hong Kong</td><td style="text-align: right;;">493</td><td style="text-align: right;;">135</td><td style="text-align: right;;">427</td><td style="text-align: right;;">241</td><td style="text-align: right;;">330</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;;">Kuala Lumpur</td><td style="text-align: right;;">274</td><td style="text-align: right;;">305</td><td style="text-align: right;;">427</td><td style="text-align: right;;">215</td><td style="text-align: right;;">419</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: center;;">Jakarta</td><td style="text-align: right;;">441</td><td style="text-align: right;;">139</td><td style="text-align: right;;">128</td><td style="text-align: right;;">419</td><td style="text-align: right;;">499</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: center;;">Siem Reap</td><td style="text-align: right;;">242</td><td style="text-align: right;;">321</td><td style="text-align: right;;">400</td><td style="text-align: right;;">374</td><td style="text-align: right;;">273</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: center;;">Yangon</td><td style="text-align: right;;">243</td><td style="text-align: right;;">399</td><td style="text-align: right;;">319</td><td style="text-align: right;;">138</td><td style="text-align: right;;">493</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;;">Colombo</td><td style="text-align: right;;">363</td><td style="text-align: right;;">487</td><td style="text-align: right;;">401</td><td style="text-align: right;;">448</td><td style="text-align: right;;">141</td></tr></tbody></table><p style="width:7.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Accommodation</p><br /><br />

And your Result sheet like this:

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">shorter then half-day</td><td style="font-weight: bold;text-align: center;;">half-day</td><td style="font-weight: bold;text-align: center;;">full-day</td><td style="font-weight: bold;text-align: center;;">two-day</td><td style="font-weight: bold;text-align: center;;">longer then two day</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Bangkok</td><td style="text-align: right;;">292</td><td style="text-align: right;;">128</td><td style="text-align: right;;">157</td><td style="text-align: right;;">485</td><td style="text-align: right;;">214</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;;">phuket</td><td style="text-align: right;;">244</td><td style="text-align: right;;">263</td><td style="text-align: right;;">165</td><td style="text-align: right;;">386</td><td style="text-align: right;;">258</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;;">Koh Samui</td><td style="text-align: right;;">320</td><td style="text-align: right;;">445</td><td style="text-align: right;;">191</td><td style="text-align: right;;">431</td><td style="text-align: right;;">407</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;;">Hua Hin</td><td style="text-align: right;;">228</td><td style="text-align: right;;">491</td><td style="text-align: right;;">321</td><td style="text-align: right;;">141</td><td style="text-align: right;;">424</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;;">Pattaya</td><td style="text-align: right;;">177</td><td style="text-align: right;;">222</td><td style="text-align: right;;">204</td><td style="text-align: right;;">322</td><td style="text-align: right;;">203</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;;">Chiang Mai</td><td style="text-align: right;;">193</td><td style="text-align: right;;">186</td><td style="text-align: right;;">125</td><td style="text-align: right;;">169</td><td style="text-align: right;;">139</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;;">Singapore</td><td style="text-align: right;;">304</td><td style="text-align: right;;">398</td><td style="text-align: right;;">261</td><td style="text-align: right;;">269</td><td style="text-align: right;;">221</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;;">Hong Kong</td><td style="text-align: right;;">493</td><td style="text-align: right;;">135</td><td style="text-align: right;;">427</td><td style="text-align: right;;">241</td><td style="text-align: right;;">330</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;;">Kuala Lumpur</td><td style="text-align: right;;">274</td><td style="text-align: right;;">305</td><td style="text-align: right;;">427</td><td style="text-align: right;;">215</td><td style="text-align: right;;">419</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: center;;">Jakarta</td><td style="text-align: right;;">441</td><td style="text-align: right;;">139</td><td style="text-align: right;;">128</td><td style="text-align: right;;">419</td><td style="text-align: right;;">499</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: center;;">Siem Reap</td><td style="text-align: right;;">242</td><td style="text-align: right;;">321</td><td style="text-align: right;;">400</td><td style="text-align: right;;">374</td><td style="text-align: right;;">273</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: center;;">Yangon</td><td style="text-align: right;;">243</td><td style="text-align: right;;">399</td><td style="text-align: right;;">319</td><td style="text-align: right;;">138</td><td style="text-align: right;;">493</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;;">Colombo</td><td style="text-align: right;;">363</td><td style="text-align: right;;">487</td><td style="text-align: right;;">401</td><td style="text-align: right;;">448</td><td style="text-align: right;;">141</td></tr></tbody></table><p style="width:7.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Accommodation</p><br /><br />

<tbody></tbody>
 
Last edited:

Beunite

New Member
Joined
Apr 22, 2016
Messages
4
Hi guys, thank you so much for your reply. I have studied the lookup, vlookup and index/match functions but wasn't able to make it work.

Here is a more detailed description of the problem:

On one work sheet (Calculation) I have Destination on column E, and category in Column F. It looks like this:

Calculation
E F
1 Bangkok Half-day
2 Phuket Full-day
3 Koh Samui Two-day

And in another spreadsheet (Accommodation) I have....

Accommodation
A B C D E
Duration Bangkok Phuket Koh Samui
1 Half-day 3 43 43
2 Full-day 5 61 72
3 Two-day 8 75 80

The end result should be in spreadsheet "Calculation" in column DW

Calculation
DW
1 3
2 61
3 80

Can somebody please help me with a substitute formula for ifand? An easy low maintenance solution?
 

Beunite

New Member
Joined
Apr 22, 2016
Messages
4

ADVERTISEMENT

For better lay out:

Calculation
E F
1 Bangkok Half-day
2 Phuket Full-day
3 Koh Samui Two-day
Accommodation
A B C D E
Duration Bangkok Phuket Koh Samui
1Half-day 34343
2 Full-day 56172
3Two-day87580
Calculation (formula retreives answer in column DW)
DW
13
261
380
Calculation (end result)
E FDW
1 Bangkok Half-day3
2 Phuket Full-day61
3 Koh Samui Two-day80

<colgroup><col span="5"></colgroup><tbody>
</tbody>
 

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
512
Office Version
  1. 365
Platform
  1. Windows
Accommodation Table:

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">S.No</td><td style=";">                       Country
Duration</td><td style="font-weight: bold;text-align: center;;">Bangkok</td><td style="font-weight: bold;text-align: center;;">phuket</td><td style="font-weight: bold;text-align: center;;">Koh Samui</td><td style="font-weight: bold;text-align: center;;">Hua Hin</td><td style="font-weight: bold;text-align: center;;">Pattaya</td><td style="font-weight: bold;text-align: center;;">Chiang Mai</td><td style="font-weight: bold;text-align: center;;">Singapore</td><td style="font-weight: bold;text-align: center;;">Hong Kong</td><td style="font-weight: bold;text-align: center;;">Kuala Lumpur</td><td style="font-weight: bold;text-align: center;;">Jakarta</td><td style="font-weight: bold;text-align: center;;">Siem Reap</td><td style="font-weight: bold;text-align: center;;">Yangon</td><td style="font-weight: bold;text-align: center;;">Colombo</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="font-weight: bold;;">shorter then half-day</td><td style="text-align: right;;">125</td><td style="text-align: right;;">440</td><td style="text-align: right;;">218</td><td style="text-align: right;;">407</td><td style="text-align: right;;">103</td><td style="text-align: right;;">444</td><td style="text-align: right;;">49</td><td style="text-align: right;;">87</td><td style="text-align: right;;">429</td><td style="text-align: right;;">374</td><td style="text-align: right;;">380</td><td style="text-align: right;;">482</td><td style="text-align: right;;">492</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="font-weight: bold;;">half-day</td><td style="text-align: right;;">313</td><td style="text-align: right;;">383</td><td style="text-align: right;;">481</td><td style="text-align: right;;">135</td><td style="text-align: right;;">148</td><td style="text-align: right;;">64</td><td style="text-align: right;;">217</td><td style="text-align: right;;">5</td><td style="text-align: right;;">182</td><td style="text-align: right;;">95</td><td style="text-align: right;;">55</td><td style="text-align: right;;">334</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="font-weight: bold;;">full-day</td><td style="text-align: right;;">236</td><td style="text-align: right;;">85</td><td style="text-align: right;;">480</td><td style="text-align: right;;">64</td><td style="text-align: right;;">163</td><td style="text-align: right;;">57</td><td style="text-align: right;;">240</td><td style="text-align: right;;">209</td><td style="text-align: right;;">298</td><td style="text-align: right;;">84</td><td style="text-align: right;;">471</td><td style="text-align: right;;">74</td><td style="text-align: right;;">411</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="font-weight: bold;;">two-day</td><td style="text-align: right;;">283</td><td style="text-align: right;;">138</td><td style="text-align: right;;">317</td><td style="text-align: right;;">306</td><td style="text-align: right;;">345</td><td style="text-align: right;;">191</td><td style="text-align: right;;">424</td><td style="text-align: right;;">81</td><td style="text-align: right;;">44</td><td style="text-align: right;;">431</td><td style="text-align: right;;">109</td><td style="text-align: right;;">292</td><td style="text-align: right;;">379</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="font-weight: bold;;">longer then two day</td><td style="text-align: right;;">267</td><td style="text-align: right;;">421</td><td style="text-align: right;;">350</td><td style="text-align: right;;">101</td><td style="text-align: right;;">272</td><td style="text-align: right;;">204</td><td style="text-align: right;;">189</td><td style="text-align: right;;">213</td><td style="text-align: right;;">378</td><td style="text-align: right;;">362</td><td style="text-align: right;;">451</td><td style="text-align: right;;">139</td><td style="text-align: right;;">187</td></tr></tbody></table><p style="width:7.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Accommodation</p><br /><br />

Result Sheet:

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>DW</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Country</td><td style="font-weight: bold;text-align: center;;">Day</td><td style="font-weight: bold;text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Bangkok</td><td style="text-align: center;;">shorter then half-day</td><td style="text-align: right;;">125</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Calculation</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">DW2</th><td style="text-align:left">=VLOOKUP(<font color="#0000FF">F2,Accommodation!$B$2:$O$6,MATCH(<font color="#FF0000">E2,Accommodation!$B$1:$O$1,0</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 

Beunite

New Member
Joined
Apr 22, 2016
Messages
4
Hi Nishant 94,

This was fantastic. Such a wonderful formula. It works like a beauty. You have saved me tons of time.

Big thank you and my appreciation for your help:)

Kind regards
Beunite

p.s great forum and I will come back here next time.


 

Forum statistics

Threads
1,141,130
Messages
5,704,454
Members
421,350
Latest member
jake9951

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top