# Return different result based on cell contents?

#### Eat More Bacon

##### Member
If I use index match to refer to column A, how can I return column B if there is a value in column B, but return column C if there is no value in column B?

#### Attachments

• 8.3 KB Views: 5

#### p45cal

##### Well-Known Member
in pre Office365 (long winded):
Code:
``=IF(INDEX(\$B\$1:\$B\$3,MATCH(G10,\$A\$1:\$A\$3,0))="",INDEX(\$C\$1:\$C\$3,MATCH(G10,\$A\$1:\$A\$3,0)),INDEX(\$B\$1:\$B\$3,MATCH(G10,\$A\$1:\$A\$3,0)))``
This might work pre Office365 (I can't test) but may need to be array-entered (Ctrl+Shift+Enter rather than just Enter when committing the formula to the sheet):
Code:
``=INDEX(IF(\$B\$1:\$B\$3="",\$C\$1:\$C\$3,\$B\$1:\$B\$3),MATCH(G10,\$A\$1:\$A\$3,0))``
In Offices365:
Code:
``=LET(a,MATCH(G10,\$A\$1:\$A\$3,0),b,INDEX(\$B\$1:\$B\$3,a),IF(b="",INDEX(\$C\$1:\$C\$3,a),b))``
Pre Office365 without index/match:
Code:
``=IF(VLOOKUP(G10,\$A\$1:\$C\$3,2,0)="",VLOOKUP(G10,\$A\$1:\$C\$3,3,0),VLOOKUP(G10,\$A\$1:\$C\$3,2,0))``
where G10 contains what you're looking up.
I'm sure there'll be more elegant ways.

#### Attachments

• 12.3 KB Views: 8
Last edited:

#### Eat More Bacon

##### Member
I'm using Excel 2007.
Thanks for your tips. i notice there is one variable I forgot to mention.
G10 (my lookup) can be a value in either column A or B.
If it is a value in column B, I want to return C.
If it is a value in column A, I want to return Column B, unless Column B is empty, then I want to return Column C.

#### p45cal

##### Well-Known Member
Not sure if I've got the logic right, so check:
Code:
``=IFERROR(IF(ISBLANK(VLOOKUP(G10,\$A\$1:\$B\$4,2,0)),VLOOKUP(G10,\$A\$1:\$C\$4,3,0),VLOOKUP(G10,\$A\$1:\$B\$4,2,0)),VLOOKUP(G10,\$B\$1:\$C\$4,2,0))``

Thanks