Nested IF with INDEX (MATCH)

Emma W.

Hi there!

Hoping to get some help solving an index match issue I am working through.

I want to use a formula that will pull a rental rate, from a rate card, based on the rental duration. There are 8 different rental rates possible for each item. Currently I am using,

=IF([Rental duration cheat]@row = 1, INDEX({1 Week Rental}, MATCH(ITEM@row, {Item_Ref}, 0)),(IF([Rental duration cheat]@row = 2, INDEX({2 Week Rental}, MATCH(ITEM@row, {Item_Ref}, 0))), IF([Rental duration cheat]@row = 3, INDEX({SB Master Overly Rate Card Range 1}, MATCH(ITEM@row, {Item_Ref}, 0)))))

At first I got the "Incomplete Argument Set"

I think I fixed that and am now getting "#UNPARSEABLE"

Is this solvable? Am I trying to ask it to look for too many different variables? I want to expand the formula to cover all 8 possibilities, but have paused until I can make 3 work.

Any help, including "not possible", would be greatly appreciated!!

Emma

Brief Description for each reference used in the formula

[Rental duration Cheat] - I am using a helper column to assign a rental duration even if the item is a flat rate. If the rental duration is blank to return a value of 1. We will be using the 1 week rental rate will double as a flat rate rental rate.

{1 Week Rental} - linking to master rate card, there are 8 different columns for each rental duration option

{Item_Ref} - Rate card is itemized, the pricing sheet identifies each item to a unique space. We need a cost by item, by space.

Genevieve P.

Hi @Emma W.

This should be possible! It looks like potentially there are a few extra parentheses in here. Let me first outline the structure:

=IF([Rental duration cheat]@row = 1, Bring Back 1 week data,

IF([Rental duration cheat]@row = 2, Bring Back 2 week data,

and so on.

In your instance, bringing back the data is through an INDEX(MATCH formula:

=IF([Rental duration cheat]@row = 1, INDEX(--(MATCH(--)),

IF([Rental duration cheat]@row = 2, INDEX(--(MATCH(--)),

...notice how many parentheses there are here, you don't need them around the IF statement.

Let's translate that to your current formula:

=IF([Rental duration cheat]@row = 1, INDEX({1 Week Rental}, MATCH(ITEM@row, {Item_Ref}, 0)),

IF([Rental duration cheat]@row = 2, INDEX({2 Week Rental}, MATCH(ITEM@row, {Item_Ref}, 0)),

IF([Rental duration cheat]@row = 3, INDEX({SB Master Overly Rate Card Range 1}, MATCH(ITEM@row, {Item_Ref}, 0))

)))

^The last three closing parentheses are for the 3 IFs. If you're not sure how many closing parentheses you need, you can remove all end parentheses then hit "enter" and Smartsheet will add in the correct number for you!

Let me know if this makes sense and if the first three are working for you, now. If not, it would be helpful to see a screen capture of the formula open in your sheet, but please block out sensitive data.

Cheers,

Genevieve