VLOOKUP combined with OR? Looking to display a date from either of the three spreadsheets?
Here comes an interesting question.
I am trying to display a date from either of the three spreadsheets (iOS iPod Touch or iPhone or Apple Watch). The date is in row 3. I am matching it up to the project number in row. I am using VLOOKUP. I am also using IFERROR, so that the field will only display a date, if a date is there. If not date, the field will be blank (it should not display "not match).
The formula works if I point to just one spreadsheet. But I want to point to either of the three spreadsheets. Is that possible?
Later on, I want to only show the most recent date (not the date furthest down on the spreadsheet, which is what it is currently doing). I tried MAX, but it won't work.
In regards to the first challenge, I tried to enter OR in my formula, but it will not work. Where does OR need to go to make this work?
Thanks, Barbara
=IFERROR(VLOOKUP(OR([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3,
" ")))
=IFERROR(VLOOKUP(OR([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3), " "))
=IFERROR(VLOOKUP(OR([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3)), " ")
=IFERROR(OR(VLOOKUP([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3)), " ")
=IFERROR(OR(VLOOKUP([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3), " "))
=IFERROR(IF(OR(VLOOKUP([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3), " ")))
=IFERROR(IF(OR(VLOOKUP([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3)), " "))
=IFERROR(IF(OR(VLOOKUP([Project Number]@row, {DAX iOS iPod Touch Project#}, 3, {DAX iOS
iPhone Inventory Range 1}, 3, {DAX iOS Apple Watch Inventory Range 1}, 3))), " ")
Best Answers
-
Hey @Barbara Witt
I have a possible solution path. I'm sure there are others. Whenever I wanted to check if a cell is blank in a cross sheet, I use countifs. When I want to index/match (I prefer to vlookup) with multiple criteria, I use Index(Collect).
=IF(COUNTIFS({Sheet1 Date}, ISDATE(@cell), {Sheet 1 Project Number}, [Project Number]@row) > 0, INDEX(COLLECT({Sheet 1 Date}, {Sheet1 Date}, ISDATE(@cell), {Sheet 1 Project Number}, [Project Number]@row), 1), IF(COUNTIFS({Sheet 2 Date}, ISDATE(@cell), {Sheet 2 Project Number}, [Project Number]@row) > 0, INDEX(COLLECT({Sheet 2 Date}, {Sheet 2 Date}, ISDATE(@cell), {Sheet 2 Project Number}, [Project Number]@row), 1), IF(COUNTIFS({Sheet 3 Date}, ISDATE(@cell), {Sheet 3 Project Number}, [Project Number]@row) > 0, INDEX(COLLECT({Sheet 3 Date}, {Sheet 3 Date}, ISDATE(@cell), {Sheet 3 Project Number}, [Project Number]@row), 1))))
Note my ranges are all single columns. You'll have to build each one.
The nice thing with Collect is it is easy to put a MAX in front of it to eventually get you the MAX date.
See if this first one works for you.
-
You won't get any No's. Your criteria says =Yes.
To get a No, let's simply do an Index/Match. You would only have one criteria so the MATCH function is all you need (I always prefer Index/Match to Vlookup - it helps smartsheet performance)
Your formula works - I was able to replicate the Invalid Data error when I had no matching data. As soon as I copied the formula down to a row that did have matching data it returned the Yes
Are we ditching the COUNTIFS for this column? If yes then
=INDEX({DAX Engagement Tracking Site Survey Complete},MATCH([Project Number]@row,{DAX Engagement Tracking Project Number},0))
The MATCH function provides the row number in the list that INDEX needs. The zero in the MATCH function says the data is unsorted. You always need that.
-
Hey @Barbara Witt
You're right! It is a Join/Collect we need. We set it up just like an INDEX/Collect, but Join in place of INDEX and your comma at the end, as the delimiter, in place of the '1'. This will join everything that the COLLECT function collects.
Try that and of course, shout out to me if you get stuck. I'll probably be on tonight.
Kelly
-
Join has the option for a delimiter. You should be able to enclose the delimiter in between the double )) parentheses you have before your IFERROR spaces. Insert something like
=IFERROR(JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), " , "), " ")
The helper text in the formula should verify this is the JOIN delimiter formula position
If your column is set to text-wrap, your delimiter can be a line break. In place of the " , " add CHAR(10) instead. No quotes around the CHAR(10). You must be in a text wrap column or cell to see the line break.
PS (yes it's morning. I'm in central time)
Answers
-
Hey @Barbara Witt
I have a possible solution path. I'm sure there are others. Whenever I wanted to check if a cell is blank in a cross sheet, I use countifs. When I want to index/match (I prefer to vlookup) with multiple criteria, I use Index(Collect).
=IF(COUNTIFS({Sheet1 Date}, ISDATE(@cell), {Sheet 1 Project Number}, [Project Number]@row) > 0, INDEX(COLLECT({Sheet 1 Date}, {Sheet1 Date}, ISDATE(@cell), {Sheet 1 Project Number}, [Project Number]@row), 1), IF(COUNTIFS({Sheet 2 Date}, ISDATE(@cell), {Sheet 2 Project Number}, [Project Number]@row) > 0, INDEX(COLLECT({Sheet 2 Date}, {Sheet 2 Date}, ISDATE(@cell), {Sheet 2 Project Number}, [Project Number]@row), 1), IF(COUNTIFS({Sheet 3 Date}, ISDATE(@cell), {Sheet 3 Project Number}, [Project Number]@row) > 0, INDEX(COLLECT({Sheet 3 Date}, {Sheet 3 Date}, ISDATE(@cell), {Sheet 3 Project Number}, [Project Number]@row), 1))))
Note my ranges are all single columns. You'll have to build each one.
The nice thing with Collect is it is easy to put a MAX in front of it to eventually get you the MAX date.
See if this first one works for you.
-
Yeah, Kelly, it's working. and where do I add the MAX for MAX date?
Also, could you tell me what the logic behind the formula is?
"IF ( >0, 1)" What does that mean?
COUNT if Sheet1 Date is a date (if the date is in the row, which matches up to the project number).
If not, "INDEX" What does that mean?
COLLECT Sheet1 Date if Sheet1 Date is a date and it's Sheet1 Project Number matches to Project Number at row". Is that worded correctly? Do you use the word "if", when I collect data?
If you don't know, no worries.
Most importantly I would like to inquire where the MAX goes. I tried the MAX before and it will not work for me. The other times a tried MAX it was with VLOOKUP and it would not work: IFERROR(MAX(VLOOKUP([Project Number]@row, {DAX iOS Inventory Project Number to Ship Date}, 3)), " "). I placed MAX in all kind of places, but no luck.
And where does MAX go in your formula above?
Thanks so much, Barbara
-
And I have this question, too, Kelly.
The formula above worked well on a date. Now I am trying the same on a field, which is not a date, but its not working. Its the same scenario, the only difference is that the field is not a date. It's a checkbox with "yes" and "no". This is what I tried, but it's not working. Any thoughts on how to use the same formula with a checkbox field? What about a standard text field?
=IF(COUNTIFS({DAX Engagement Tracking Site Survey Complete}, NOT(ISDATE(@cell)), {DAX Engagement Tracking Project Number}, [Project Number]@row), >0, INDEX(COLLECT({DAX Engagement Tracking Site Survey Complete}, {DAX Engagement Tracking Site Survey Complete}, NOT(ISDATE(@cell)), {DAX Engagement Tracking Project Number}, [Project Number]@row), 1))
Thanks so much for your continued assistance. Barbara
-
Hey
The CountIfs formula was >0, and this was only to see if the cell was blank. If it wasn't blank, the INDEX(COLLECT) formula executed. The INDEX function - the official explanation is "Returns an item from a collection based on provided row and column indexes" and is is comprised of a few components INDEX(range, row_index, [column_index]). The first is the range - which the Collect function provides. The next component is the row index. The function is looking for a number of what row to grab from the list. This is the "1" at the end of the Index term.
Because we want the MAX of what we're collecting, the Max sits outside the Collect. Max can be used as a criteria within the collect.
=IF(COUNTIFS({Sheet1 Date}, ISDATE(@cell), {Sheet 1 Project Number}, [Project Number]@row) > 0, INDEX(MAX(COLLECT({Sheet 1 Date}, {Sheet1 Date}, ISDATE(@cell), {Sheet 1 Project Number}, [Project Number]@row)), 1), IF(COUNTIFS({Sheet 2 Date}, ISDATE(@cell), {Sheet 2 Project Number}, [Project Number]@row) > 0, INDEX(MAX(COLLECT({Sheet 2 Date}, {Sheet 2 Date}, ISDATE(@cell), {Sheet 2 Project Number}, [Project Number]@row)), 1), IF(COUNTIFS({Sheet 3 Date}, ISDATE(@cell), {Sheet 3 Project Number}, [Project Number]@row) > 0, INDEX(MAX(COLLECT({Sheet 3 Date}, {Sheet 3 Date}, ISDATE(@cell), {Sheet 3 Project Number}, [Project Number]@row)), 1))))
Also, check out the INDEX/MATCH instead of using VLOOKUP. It is an extremely robust formula and easier on smartsheet performance
On using this formula in a checkbox column - it depends on the data collected - the data range that is collected in the first term. Is that data boolean? A checkbox column is expecting that. Yes on regular Text/Number fields.
Kelly
-
Thank you very much, Kelly, for the education. I will study it, so I can be more independent in the future.
The MAX worked out. I did not know where to put the closing bracket ), thus it would not calculate for me earlier.
I misspoke about the checkbox. It's actually a dropdown field with yes/no, thus a regular Text/Number field. I have not worked with boolean fields yet, but I am about to add a harvey ball to my spreadsheet to showcase completion status (based on scoped devices vs. installed devices).
Since I am not looking at a date field, but a Text/Number field instead, I tried "= Yes" and I tried "Yes" (and a few other variations) at the same location where we had {Sheet1 Date}, ISDATE(@cell), but that did not work. How should "yes" be displayed?
=IF(COUNTIFS({DAX Engagement Tracking Site Survey Complete}, "Yes", {DAX Engagement Tracking Project Number}, [Project Number]@row), >0, INDEX(COLLECT({DAX Engagement Tracking Site Survey Complete}, {DAX Engagement Tracking Site Survey Complete}, "Yes", {DAX Engagement Tracking Project Number}, [Project Number]@row), 1))
Why is it important to check for an empty field? If I don't check for an empty field and take "IF" out, what exactly remains?
=COUNTIFS({DAX Engagement Tracking Target Install Date}, ISDATE(@cell), {DAX Engagement Tracking Project Number}, [Project Number]@row), INDEX(COLLECT({DAX Engagement Tracking Target Install Date}, {DAX Engagement Tracking Target Install Date}, ISDATE(@cell), {DAX Engagement Tracking Project Number}, [Project Number]@row), 1))
Thanks again, Kelly. This is a little over my head, but I am catching up slowly.......
Best regards, Barbara
-
Hey
When you say, why check for an empty field, are you meaning the COUNTIFS? That's the easiest way I know to compare 3 fields from a different sheet with criteria and choose which of the 3 to select. So if the first is not blank, use that one. If the second is not blank, use it. And so on. There might be an easier way but it has always worked for me. In this case, if you don't have 3 fields to choose from, you do not need to check- which would remove everything about the COUNTIFS and IF.
For your text number field, try @cell="Yes". Just reading through your formula I would have expected it to work. When I have a multiple criteria formula that isn't working I start removing criteria&range one by one to see where my problem is.
-
Hi Kelly, your advice is extremely valuable.
I tried @cell = "Yes", but it says "Invalid Value". I tried (@cell) = "Yes" and (@cell = "Yes").
=IF(COUNTIFS({DAX Engagement Tracking Site Survey Complete}, @cell = "Yes", {DAX Engagement Tracking Project Number}, [Project Number]@row), >0, INDEX(COLLECT({DAX Engagement Tracking Site Survey Complete}, {DAX Engagement Tracking Site Survey Complete}, @cell = "Yes", {DAX Engagement Tracking Project Number}, [Project Number]@row), 1))
What other formatting may SmartSheet prefer? Sometimes it can be a little frustrating, but I am pulling thru.....
-
In this case, do you need the COUNTIFS? I'm also wondering what you're trying to gather? I had checked your formula for syntax, not looking at your data. This will put a Yes in your formula column assuming there is a yes with that project name. Is that what you expected?
-
Yes, that is what I need. If a no is with the project name, then I would like to see a No. Does that help?
-
You won't get any No's. Your criteria says =Yes.
To get a No, let's simply do an Index/Match. You would only have one criteria so the MATCH function is all you need (I always prefer Index/Match to Vlookup - it helps smartsheet performance)
Your formula works - I was able to replicate the Invalid Data error when I had no matching data. As soon as I copied the formula down to a row that did have matching data it returned the Yes
Are we ditching the COUNTIFS for this column? If yes then
=INDEX({DAX Engagement Tracking Site Survey Complete},MATCH([Project Number]@row,{DAX Engagement Tracking Project Number},0))
The MATCH function provides the row number in the list that INDEX needs. The zero in the MATCH function says the data is unsorted. You always need that.
-
Hi Kelly, I am sorry you have to work on Valentine's Day. I hope you have a happy one.
I am just amazed by how you are able to help me with only the information I am giving you. You are doing a fantastic job. Yes, this worked. I don't know if I will ever figure this logic out. You are a life saver.
Happy Valentine's Day. I know you are treasured by your valentine.
All the best. I am sure I'll be back later today or tomorrow :>)
-
Hi @KDM, the formula you gave me works well for another scenario, too. I used it here:
=IFERROR(INDEX({DAX Ambient Inventory Install Location}, MATCH([Project Number]@row, {DAX Ambient Inventory Project #}, 0)), " ")
Now, I would like to add a little something. Each project number could have several Install Locations and I would like all install locations to show in my field.
For the date formula above we used MAX = giving us the most recent date. Is there a way to collect all install locations for one project number and list them in one single field, maybe separated by a comma?
I guess I would use JOIN or COLLECT? Where within the formula would it go?
Thanks for your help. Best regards, Barbara
-
Hey @Barbara Witt
You're right! It is a Join/Collect we need. We set it up just like an INDEX/Collect, but Join in place of INDEX and your comma at the end, as the delimiter, in place of the '1'. This will join everything that the COLLECT function collects.
Try that and of course, shout out to me if you get stuck. I'll probably be on tonight.
Kelly
-
Good morning (or afternoon) @KDM , that worked perfectly. Thank you very much.
=IFERROR(JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row)), " ")
Do you know what I need to do, so I can add some spaces in between each location, that I am collecting? Right now it assemblies to one long word, which makes it hard to read. Preferable a comma and a space after each location (and nothing after the last location - if that is possible).
Is there a way to add a comma and a space?
Best regards, Barbara
-
Join has the option for a delimiter. You should be able to enclose the delimiter in between the double )) parentheses you have before your IFERROR spaces. Insert something like
=IFERROR(JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), " , "), " ")
The helper text in the formula should verify this is the JOIN delimiter formula position
If your column is set to text-wrap, your delimiter can be a line break. In place of the " , " add CHAR(10) instead. No quotes around the CHAR(10). You must be in a text wrap column or cell to see the line break.
PS (yes it's morning. I'm in central time)
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
