# Embedded Index and Match and If statements

#### Taneasy

##### New Member
{=IFERROR(VLOOKUP(IF(COUNTIF(Table1,INDEX(Table1[User ID], MATCH("Yes", IF(Table1[Asset]=Sheet2!\$C8,Sheet1!L\$2:L\$343),0)))>1, "Double", INDEX(Table1[User ID], MATCH("Yes", IF(Table1[Asset]=Sheet2!\$C8,Sheet1!L\$2:L\$343),0))),Table1,7,FALSE)&") "&(IF(COUNTIF(Table1,INDEX(Table1[User ID], MATCH("Yes", IF(Table1[Asset]=Sheet2!\$C8,Sheet1!L\$2:L\$343),0)))>1, "Double", INDEX(Table1[User ID], MATCH("Yes", IF(Table1[Asset]=Sheet2!\$C8,Sheet1!L\$2:L\$343),0)))), "")}

Can anyone make this formula more efficient, or write it in VBA?

I would like to use the Named ranges, however this formula has to be replicated 15 columns across and 35 rows down. The formula is correct as is and can be dragged/copied. in a table.

All of the named ranges are arrays

Thanks.

Last edited:

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Taneasy

##### New Member
Sorry the formula I added was from the clipboard and was messed up... this is the correct formula.

=IFERROR(IF(COUNTIF(Table1,INDEX(Table1[User ID], MATCH("Yes", IF(Table1[Asset]=Sheet2!\$E8,Sheet1!L\$2:L\$805),0)))>1, "Double",(VLOOKUP(INDEX(Table1[User ID], MATCH("Yes", IF(Table1[Asset]=Sheet2!\$E8,Sheet1!L\$2:L\$805),0)),Table1,7,FALSE)&") "&(INDEX(Table1[User ID], MATCH("Yes", IF(Table1[Asset]=Sheet2!\$E8,Sheet1!L\$2:L\$805),0))))), "")