Lookup on two columns.xlsx

Anyone able to provide a formula for the cell H4 on sheet "New Formula Required" to replace the formula in the same cell on the sheet "Current Method"

Thanks