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.
4 Replies
=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.
=INDEX(D$3:D$8,MAX(MMULT(COLUMN($A:$F),N($A13=$A$3:$C$8))))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. The ranges can be adapted as required.
