Forum Discussion
VLookup formula based on two columns with condition
Hi I have a list with the following fields: Email Forename Surname Job Title Department Portal Username Management Unit Full Email Address I need to create 2 vlooup columns for man...
You can try these formulas along with a reference table to determine the next higher role. Enter the formulas with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=IFERROR(INDEX($A$2:$A$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0)),"")This is the formula in cell K2.
=IFERROR(CONCATENATE(INDEX($B$2:$B$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0))," ",INDEX($C$2:$C$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0))),"")This is the formula in cell J2.
All formulas are filled down to row 7 in this example.
=IFERROR(INDEX($E$17:$E$21,MATCH(D2,$E$17:$E$21,0)+1),"")This is the formula in cell I2 which identifies the next higher role from the reference table.