# Value based Check data

Status
Not open for further replies.

#### Vignesh Veerasamy

##### Member
Hi Excel Gurus

I need output as mentioned in check column.

Thanks

#### Attachments

• 12.1 KB Views: 3

#### bosco_yip

##### Excel Ninja
Hi,

Since your Source table range C2:I8 data is generated from formula and causing Circular References error.

I converted all your C2:I8 formula result into value

Then, In J2 CSE formula copied down :

=IF(INDEX(C2:I2,LARGE(IF(C2:I2<>"",COLUMN(C\$1:I\$1)-COLUMN(B\$1)),2))&INDEX(C2:I2,LARGE(IF(C2:I2<>"",COLUMN(C\$1:I\$1)-COLUMN(B\$1)),1))="0-","To Check","No Check")

#### Attachments

• 11.7 KB Views: 3

#### Vignesh Veerasamy

##### Member
Hi,

Since your Source table range C2:I8 data is generated from formula and causing Circular References error.

I converted all your C2:I8 formula result into value

Then, In J2 CSE formula copied down :

=IF(INDEX(C2:I2,LARGE(IF(C2:I2<>"",COLUMN(C\$1:I\$1)-COLUMN(B\$1)),2))&INDEX(C2:I2,LARGE(IF(C2:I2<>"",COLUMN(C\$1:I\$1)-COLUMN(B\$1)),1))="0-","To Check","No Check")

View attachment 80460
Thank you.
I tried with same formula copy pasted all the values from formula but observing the formula is skipping the condition.

#### Attachments

• 11.2 KB Views: 0

#### Vignesh Veerasamy

##### Member
Modified the formula strange is when "A" is starting the series it is showing "To check" which is contradictory.

Formula : =IF(INDEX(B2:O2,LARGE(IF(B2:O2<>"",COLUMN(B\$1:O\$1)-COLUMN(A\$1)),2))&INDEX(B2:O2,LARGE(IF(B2:O2<>"",COLUMN(B\$1:O\$1)-COLUMN(A\$1)),1))="0-","To Check","No Check")

#### Attachments

• 11 KB Views: 2
Last edited:
Status
Not open for further replies.