Forum Discussion
Lookup from Multiple Columns
I am trying to write a formula that would return the bold cells in this sheet. It would be like a vlookup or index/match, but with how the data is structured, there has to be multiple columns for the reference columns (columns A, B, and C in table 1). Attached is a screenshot of my sheet. Ideally, I would be able to insert another column between C and D in table 1 and still have the lookup in table 2 return the correct values.
=INDEX(D$3:D$8,MATCH(1,($A$3:$A$8=$A13)+($B$3:$B$8=$A13)+($C$3:$C$8=$A13),0))You can try this formula for the data layout of the example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- Do you know if there's a way to make it so that if I want to add a reference column in table 1, I would be able to without having to add more conditions to the formulas in table 2? Eventually, I will have 50 or 100 reference columns formatted similarly to columns A:C and I don't want to have to formula with 100 conditions in it.
You can try FILTER() with MMULT() where you do not need to enter criteria for each reference cell. See the attached file.
=FILTER($D$3:$H$4,MMULT(--($A$3:$C$4=A12),SEQUENCE(COLUMNS($A$3:$C$4),,,0)))If you have BYROW() function available to your excel then below would be easier.
=FILTER($D$3:$H$4,BYROW(--($A$3:$C$4=A12),LAMBDA(x,MAX(x))))
