Using a nested IF to determine a range

Paul Newcome

Here's a little tid-bit I just figured out today... You can use a nested IF statement to output ranges/cross sheet references that are to be evaluated.

The particular use case that led to this was an INDEX/MATCH where we wanted to INDEX on a single date column, but MATCH on one of 4 different columns depending on an Asset Type. We ended up being able to use a nested IF for the "range to search in" portion of the MATCH.

=INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, IF([Asset Type]@row = "Camera", {Asset Checkout Range 1}, IF([Asset Type]@row = "Support", {Asset Checkout Range 2}, IF([Asset Type]@row = "Lighting", {Asset Checkout Range 5}, {Asset Checkout Range 6}))), 0))

I personally like this a little better than writing out four different INDEX/MATCH statements and using IFERRORs to account for the #NO MATCH issue which would have looked like this...

=IFERROR(IFERROR(IFERROR(INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 1}, 0)), INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 5}, 0))), INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 1}, 0))), INDEX({Asset Checkout Range 3}, MATCH(Barcode@row, {Asset Checkout Range 6}, 0)))

We saved 93 characters/keystrokes and only had to reference the INDEX range and the "data to match on" for the MATCH function once each instead of 4 times, and I can't say for sure, but I also feel like this might be a little more efficient on the back-end as well.

I feel like there are a lot more practical applications too.

Here is a link to the thread that prompted this...

https://community.smartsheet.com/discussion/79485/searching-across-three-columns-and-if-match-found-then-return-info-from-a-date-column

Genevieve P.

https://media.giphy.com/media/1ym5LJ17vp77BL8X5O/giphy.gif

Well, this is amazing. Thank you for sharing, @Paul Newcome!!

Paul Newcome

I just can't believe I never tried it before. I would normally have gone the IFERROR route, but the thought didn't even cross my mind this time. I'm glad it didn't because I like this way better. Haha

L_123

@Paul Newcome

Very Nice! This reminded me of something, and I spent 20 minutes trying to find our discussion about it haha.

https://community.smartsheet.com/discussion/29386/unexpected-behavior-with-collect-formula#latest

Paul Newcome

@L@123 Haha. I completely forgot about that one. Here in a few days it will be exactly 3 years ago. Hahaha.

I still haven't completely wrapped my head around the logic of that one. Maybe I need to just build out a sheet and paste your formula in so I can play around with it myself.

L_123

@Paul Newcome I"m not sure if it works the same as back then. That whole project pretty much got wiped when they added the move/copy row functionality. I'm going to play around with it a bit. I deleted all the original files, so i'd have to recreate it.

L_123

https://app.smartsheet.com/b/publish?EQBCT=ca4670a6cfb541b8a74d2d30e3bb6c02

it works even better than it used to. Now you can just slap a "true" in there and it works like a charm.