Using a nested IF to determine a range

Paul NewcomePaul Newcome ✭✭✭✭✭
edited 07/07/21 in Best Practice
05/18/21 Edited 07/07/21

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([email protected], 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([email protected], {Asset Checkout Range 1}, 0)), INDEX({Asset Checkout Range 3}, MATCH([email protected], {Asset Checkout Range 5}, 0))), INDEX({Asset Checkout Range 3}, MATCH([email protected], {Asset Checkout Range 1}, 0))), INDEX({Asset Checkout Range 3}, MATCH([email protected], {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...


thinkspi.com

Comments

Sign In or Register to comment.