IF and VLOOKUP

Can anyone help me with these two formulas I am trying to do:

1. IF a cell with a checkbox is true, then run this VLOOKUP formula, otherwise 0.

2. IF a cell contains this text, run this VLOOKUP formula, or IF the same cell contains this text2, run this VLOOKUP formula, or IF the same cell contains this text3, run this VLOOKUP formula, etc, etc, otherwise 0.

Thanks

Cheers

Richard

Find more posts tagged with

Formulas

Comments

Paul Newcome

Can you provide more details and some screenshots?

Richard

Hi Paul

I'm sorted with 1. I think.

2. This is the formula I have for one IF and it works - =IF([Venue Space]1 = "Hall 4.0", VLOOKUP([Supply Code]1, {VenuePriceList - FiraGranViaBarcelona - VM Range 2}, 15, false))

I just need to do ORs for all these in the same cell [Venue Space]1, so depending on the text in that cell, it returns a different VLOOKUP (same referenced sheet and row, just different column -

=IF([Venue Space]1 = "Hall 6.0", VLOOKUP([Supply Code]1, {VenuePriceList - FiraGranViaBarcelona - VM Range 2}, 16, false))

=IF([Venue Space]1 = "Hall 8.0", VLOOKUP([Supply Code]1, {VenuePriceList - FiraGranViaBarcelona - VM Range 2}, 17, false))

=IF([Venue Space]1 = "Hall 8.1", VLOOKUP([Supply Code]1, {VenuePriceList - FiraGranViaBarcelona - VM Range 2}, 18, false))

=IF([Venue Space]1 = "Atrium", VLOOKUP([Supply Code]1, {VenuePriceList - FiraGranViaBarcelona - VM Range 2}, 19, false))

=IF([Venue Space]1 = "Walkway", VLOOKUP([Supply Code]1, {VenuePriceList - FiraGranViaBarcelona - VM Range 2}, 20, false))

Does that make sense?

Thanks

Paul Newcome

This would be a nested IF statement. Not an OR statement. Basically you would take all of those and combine them into a string using the layout below:

=IF(Criteria 1, Result 1, IF(Criteria 2, Result 2, IF(Criteria 3, Result 3, IF(Criteria 4, Result 4, Otherwise Result 5))))

That formula is going to get rather long, but it will work.

If you wanted to shorten it up a little bit, you could expand your table to include your Venue Space data and use an INDEX/MATCH to search.

Richard

Thanks Paul, appreciated.

Paul Newcome

Happy to help.

Sign in to join the conversation!

Quick Links

Trending discussions

Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
Dashboard not updating
Hello Smartsheet Community! I created a formula that tracks the number of days away from the deadline that I posted on my dashboard as a visual for my students. =IF([Deadline Date]@row < TODAY(), "🔴 Past Due (" + ABS([Deadline Date]@row - TODAY()) + " days ago)", IF([Deadline Date]@row = TODAY(), "🟠 Due Today",…
Sheet Summary Formula Help
Hello, I am trying to get this formula to work but am not having any luck. When I remove the last @cell….text below the formula works great. When I add it in I get an error. Any ideas? =COUNTIFS([ACCT READINESS STATUS PLV]:[ACCT READINESS STATUS PLV], OR(@cell = "PENDING LOCAL CAH", @cell = "ADDITIONAL REVIEW NEEDED LOCAL…