Forum Discussion
Lookup Formula with multiple column
Hi There, I am trying to return data in a particular column but the lookup value could be in any of a number of columns and I am not sure the best way to pull this in. I have shown a snip with an example, I want to look up column B and find the value in the Row No., column E, but the data I am looking up will be in any of the columns G-AP. You can see in this example if I look up 863101, its in column F and I should be returning 190600 and if I look up 863203 its in column H and I should be returning 194000.
I tried to transpose it to see if that would make more sense, but it doesn't.
Can anyone help?
12 Replies
I liked the way you dealt directly with the 2D totals array. MMULT works well for the OR condition.
I have used your workbook for a couple more dynamic array formulas but they are probably moving even further from the OP's comfort zone!
= MAP(CL, LAMBDA(CL₁, LET( ORλ, LAMBDA(x, OR(x)), found?, BYROW(totalling=CL₁, ORλ), FILTER(rowNo, found?, "") )))or with insider beta
= MAP(CL, LAMBDA(CL₁, LET( found?, BYROW(totalling=CL₁, OR), FILTER(rowNo, found?, "") )))Thanks for the demonstration of TOCOL function! Still getting myself familiar with MS365 here.
- Thank you so much Rachel, this has worked although I am not going to pretend that I know how! 🙂
- Thanks for your feedback !
= LET( row, TOCOL(IF(totalling, rowNo, NA()), 3), tot, TOCOL(totalling, 3), XLOOKUP(GL, tot, row) )This 365 formula converts the 'Totalling' array to a single column omitting blanks. It uses broadcasting to do the same with RowNo. From there, it is a simple XLOOKUP.
- thank you. I am not that familiar with 365 formula so think this is a bit advanced for me, but I appreciate your response
