INDEX Formula to pick up one or other column

[Rookie2016]

All,

I am trying to use the index function to pick up results from one column or another. If column 8 is blank, then use column 4. But even though column 8 is blank my formula returns a "0". In addition, if the reference # is not in the first sheet, I need blanks "" instead of #N/A.

Currently my Formula is:

=IFERROR(INDEX(Sheet1!$1:$1048576,MATCH(Sheet2!A3,Sheet1!A:A,0),8),INDEX(Sheet1!$1:$1048576,MATCH(Sheet2!A3,Sheet1!A:A,0),4)).

I've attached a sheet with desired results. Any help I could get is appreciated!

[Fluff13]

How about

Please Login or Register  to view this content.

[WideBoyDixon]

Try this instead:

Please Login or Register  to view this content.

WBD

[Glenn Kennedy]

or indeed:

=IFERROR(IF(INDEX(Sheet1!H:H,MATCH(A3,Sheet1!A:A,0))=0,INDEX(Sheet1!D:D,MATCH(A3,Sheet1!A:A,0)),INDEX(Sheet1!H:H,MATCH(A3,Sheet1!A:A,0))),"")

[AliGW]

This is because the lookup value matches in the first lookup, so it never moves on to the second.

What is the name of the column that you are referring to as 4? To me (and Excel), that's the OWNER column - is this what's intended?

It's very bad practice to use an entire worksheet range in a lookup.

[Rookie2016]

Thank you all so much for the help! All three suggestions worked perfectly.

[Fluff13]

Glad to help & thanks for the feedback.