If a cell contains a different word can you get it to do a vlookup on a different column

[daza34]

Hi,

I have rows of data that has a specific word in them that has to look up against a certain column

23_OCC Collaborate Needs to look up to Column BP on a separate sheet
23_OCC VOICE Needs to look up on Column BQ on a separate sheet

If i look at just one column both rows will get the same result which is not the case, also need the look up to show blank if there are not any values in that column.

Office 365

 

[6StringJazzer]

You don't give us enough information about where this value is, where the result comes from, the other sheet name, or anything else about your data, or your version of Excel. Here is a solution for later versions with some assumptions.

=IFERROR(XLOOKUP(A1,IF(A1="23_OCC Collaborate",Sheet2!BP:BP,IF(A1="23_OCC VOICE",Sheet2!BQ:BQ)),Sheet2!X:X),"")

A1 is where this value is, Sheet2 is the name of the other sheet, X is the column to retrieve the result from.