Excel VLOOKUP using 2 colums from a table array

Question

I have the below VLOOKUP formula

=IFERROR(VLOOKUP(@B5:B64,dbVEH!$A$2:$E$296,5,FALSE),"")

I am trying to create a formula that searches 2 columns on the table array. I have the range A$2:$E$296 however it only matches the A column and delivers the result from the E column.

I am trying to create a formula so that if Cell B5 on my main sheet = anything in column A OR anything in column B on the "dbVEH" sheet then display the result from the 5th E column on "dbVEH" in cell B5 on my main sheet.

My reasoning behind this is that I'm trying to cover alternative spellings inputted into B5:B64 on my main sheet. One cell could have "Thomas" and another cell could have "Tomas" but they both need to equal the same result. My table on sheet dbVEH has the first column "Thomas" and the second column "Tomas" to cover the different spelling types.

Answer 1

VLOOKUP will not do that. Use FILTER:

=@FILTER(dbVEH!$E$2:$E$296,(B5=dbVEH!$A$2:$A$296)+(B5=dbVEH!$B$2:$B$296))