Embedded Index and Match and If statements

Taneasy

New Member
Joined
Mar 3, 2012
Messages
7
{=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:

Some videos you may like

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
Joined
Mar 3, 2012
Messages
7
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))))), "")
 

Watch MrExcel Video

Forum statistics

Threads
1,095,982
Messages
5,447,691
Members
405,463
Latest member
Tommy5

This Week's Hot Topics

Top