Forum Discussion
Helper ISkipping blanks in valuelookup
Hello,
Is it possible to use valuelookup and skip zeros/blanks in order to return only the whole number associated to an ID. I have a column that only some of the unique IDs have a value per row. The dataset has many duplicated IDs which is why I'm getting the 'multiple values were supplied where a single value was expected'. Is it possible to use NoBlankrows or another type filter with valuelookup to bypass the blanks in the column?
Thank you for the help and clarification.
MSW You could use CONCATENATEX instead of MAXXX to pull back multiple values. I'm having some issues trying to visualize your setup. You might try adding an index maybe? I'm not understanding what ties the second 123 in the first table to the second 123 in the second table. You'd have to have everything in the same order and use an index.
3 Replies
- Greg_Deckler
Community Champion
MSW Assuming you are talking about LOOKUPVALUE function, use MAXX(FILTER(...),...) instead. More reliable and allows you to explicitly control the filtering.
MSWGreg_Deckler Thanks that works except that if I do not want the max value and I want the value associated to each row by ID.
IE:
Table 1:
Customer ID
123 345 123 567 Table 2:
Customer ID
Merged
123 123 789 345 345 134 123 123 876 567 567 214 I want to return the merged values to table one but the only way I have to connect the two is by customerID. Is it possible to look up the value and return it based on the row? The other option I have that does not appear to be working is in another table I have the merged column that is already associated to each customer ID. Greatly appreciate the help
- Greg_Deckler
MSW You could use CONCATENATEX instead of MAXXX to pull back multiple values. I'm having some issues trying to visualize your setup. You might try adding an index maybe? I'm not understanding what ties the second 123 in the first table to the second 123 in the second table. You'd have to have everything in the same order and use an index.