If and Index reference with two columns

[Rookie2016]

All, I'm struggling with a formula that helps me index a reference to values in two columns. I know how to index to one column but I need a formula that says if column G doesn't have a value then pick column H. For the record, either one of these columns will have a value. I'm trying to get the value to populate in a second sheet.

The formula M'm using currently is this =IF(A2="","",INDEX(Sheet1!$1:$1048576,MATCH(A2,Sheet1!J:J,0),7)) but if column G (7) doesn't have a value this doesn't work.


I've attached a sample of what i'm referring to in an excel sheet as well. Sheet1 shows a sample of the data and sheet2 is where the results would go to.

I'd appreciate any help.


Thanks,
Rookie

[LeoTaxi]

Maybe

Please Login or Register  to view this content.

Cheers
Leo

[FDibbins]

Another option...
=IF(A2="","",MAX(INDEX(Sheet1!$G:$G,MATCH(Sheet2!$A2,Sheet1!$J:$J,0)),INDEX(Sheet1!$H:$H,MATCH(Sheet2!$A2,Sheet1!$J:$J,0))))

[FlameRetired]

Another option array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[FDibbins]

Another option array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

..Sweet ..

[FlameRetired]

Thanks.

Another way does not have to be array entered.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Try this ARRAY formula
In C2 , then drag down

Please Login or Register  to view this content.

Confirm formula with Ctrl+Shift+Enter

[Rookie2016]

Fdibbins and FlameRetired - I'd like to use either of your formulas but I'm having an issue when the value in column G (account 1) is negative. The value in column H does work whether it's negative or positive.


I'm not using formulas with arrays in this case because I have to sort the formulas.

Thanks,
Rookie

[Rookie2016]

Nevermind! i figured out the issue! thank y'all for the help!

[FlameRetired]

Good deal. Thank you for updating us.

[FDibbins]

Always a good feeling when you resolve these yourself

Thanks for the feedback