I was wondering if there is a way we can use FINDITEM to find a value from multiple columns.
The way FINDITEM works is by comparing a list with a specific line item (column). However, in this case, I need to be able to find an item that may be in column 1 or column 2 or 3. Is there a way to achieve this? Any other option if FINDITEM does not work?
I representation of the data might help here just so we understand the requirements and be able to help. I wouldn't suggest doing what @ankit_cheeni stated as you are doing the finditem() multiple times (doing a finditem for the isnotblank and then doing it again). Instead, I would suggest breaking it up, creating a line item for each column and do the single finditem() with the formula being: if isnotblank(col A) then finditem() else blank. Yes, it is more cells, but it is far more performant.
@rob_marshall I did recommend creating separate line items for each FINDITEM, which would replace all FINDITEMs in the formula. Not sure how is that different from what you are suggesting.
Also, if you do 'if isnotblank(col A) then finditem() else blank' you are assuming that if there is a text, it will be present in the list and then you'd create another IF THEN ELSE to consolidate all 3 columns in one. It would be more inclusive if you do separate FINDITEMs and check on the result of finditem in the final line.
I must have missed the last sentence and just looked at the formula you wrote as an example. As for the isnotblank bit, it depends on the data as to which one to put first for the THEN vs. the ELSE (basically which criteria happens most often should be first). As for separating the Finditems(), that is exactly what I said.
The reason I wanted to have this multiple find item is to map is to solve this specific issue.
I have a module where I have the GL Account and the columns have different levels. It is a ragged hierarchy so we are not sure which column the parent is so i am trying to do a multi finditem. Any other solution would be appreciated.
The reason i need it is becuase actuals come below the budget account and there is no specific rule regarding how many levels below. some may be one level, which is easier because the parent would be the account i am looking for, however, in some cases, the parent's parent would be the one i need, that is why i was trying to do a finditem with or so that it can check col1, or col2 etc.