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

#### Beunite

##### New Member
Hi there,

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

#### Beunite

##### New Member

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 1 Half-day 3 43 43 2 Full-day 5 61 72 3 Two-day 8 75 80 Calculation (formula retreives answer in column DW) DW 1 3 2 61 3 80 Calculation (end result) E F DW 1 Bangkok Half-day 3 2 Phuket Full-day 61 3 Koh Samui Two-day 80

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

#### Nishant94

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

#### Beunite

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

Replies
44
Views
3K
Replies
4
Views
1K

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.

### Which adblocker are you using?

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

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