Get Info from cells using if, index, small and concatenate

1. Get Info from cells using if, index, small and concatenate

I have a sheet that lists first name, last name, and a column with a letter (A, B, C). I'm trying to retrieve the First and Last Name IF it contains B or C. If it contains A, it's supposed to go to the next row. I've tried many different ways and it comes up differently each time.

1) =IF(ISBLANK(Details!\$B89),"",IFERROR(INDEX(CONCATENATE(Details!B89:\$B\$200&", "&Details!C89:\$C\$200),SMALL(IF((Details!\$E\$2:Details!\$E\$200)={"B","C"},ROW(Details!\$E\$2:Details!\$E\$200)-ROW(Details!E\$2)+1),ROW(B\$1:B90)))," ")) Returns Last Name, First Name but it starts at the first person with the letter B and then EVERYONE after that and then just the "," until it hits the blank.

2) =IF(ISBLANK(Details!\$B95),"",IFERROR((SMALL(IF((Details!\$E\$2:Details!\$E\$200)={"B","C"},CONCATENATE(Details!B95:\$B\$200&", "&Details!C95:\$C\$200),ROW(Details!\$E\$2:Details!\$E\$200)-ROW(Details!E\$2)+1),ROW(B\$1:B96)))," ")) Returns a 1

3) =IF((Details!\$E\$2:Details!\$E\$200)<>"A",IF(ISBLANK(Details!\$B97),IFERROR(INDEX(CONCATENATE(Details!B97:\$B\$200&", "&Details!C97:\$C\$200),SMALL(IF((Details!\$E\$2:Details!\$E\$200)<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-ROW(Details!E\$2)+1),ROW(B\$1:B99)))," "))) Returns FALSE

4) =IF(OR(ISBLANK(Details!B93:\$B\$200),ISBLANK(Details!C93:\$C\$200)),"",IFERROR(INDEX(CONCATENATE(Details!B93:\$B\$200&", "&Details!C93:\$C\$200),SMALL(IF((Details!\$E\$2:Details!\$E\$200)<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-ROW(Details!E\$2)+1),ROW(B\$1:B92))),"")) Returns EVERYONE

5) =IF(ISBLANK(Details!\$B101),"",IF((Details!\$E\$2:Details!\$E\$200)<>"A",CONCATENATE(Details!B99:\$B\$200&", "&Details!C99:\$C\$200),SMALL((ROW(Details!\$E\$2:Details!\$E\$200)-ROW(Details!E\$2)+1),ROW(B\$1:B96)))) Returns blank cells

Does anyone have a solution for me? Thanks in advance.

2. Re: Get Info from cells using if, index, small and concatenate

Can you please post a sample workbook as it is difficult to relate your formulae without having actual data. Thank you.

3. Re: Get Info from cells using if, index, small and concatenate

Is this what you need to do?

Data starting row 2

Col A is First Name
Col B is Surname
Col C is "A","B" or "C"

formula in D2 entered with Ctrl+Shift+Enter

=INDEX(\$A\$2:\$A\$10&" " & \$B\$2:\$B\$10,SMALL(IF(\$C\$2:\$C\$10<>"A",ROW(\$C\$2:\$C\$10)-1,""),ROW(1:1)))

will produce list in D of "B" & "C" names

4. Re: Get Info from cells using if, index, small and concatenate

Thanks John. That is how my data is setup except its last name and first name. I have it so that it has "," between the last and first name. After the last person with "B" or "C", it produces a comma for a while then gives me #REF. I added IFERROR and removed the #REF but can't figure out how to remove the commas in the other cells.

=IF(ISBLANK(Details!\$B2),"",IFERROR(INDEX(Details!B2:\$B\$200&", "&Details!C2:\$C\$200,SMALL(IF(Details!\$E\$2:Details!\$E\$200<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1))),""))

5. Re: Get Info from cells using if, index, small and concatenate

The B & C columns need to be \$B\$2 and \$C\$2 in the INDEX part.

To remove the "," ......

=IF(ISBLANK(Details!\$B2),"",IFERROR(IF(INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(Details!\$E\$2:Details!\$E\$200<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(Details!\$E\$2:Details!\$E\$200<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))),""))

6. Re: Get Info from cells using if, index, small and concatenate

sorry ... you can remove the first test ...

=IFERROR(IF(INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(Details!\$E\$2:Details!\$E\$200<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(Details!\$E\$2:Details!\$E\$200<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))),"")

7. Re: Get Info from cells using if, index, small and concatenate

Thank you very much! That worked perfectly. I'll probably be back as I think I still have a few more quirks to work out.

8. Re: Get Info from cells using if, index, small and concatenate

So this worked:
=IFERROR(IF(INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(Details!\$E\$2:Details!\$E\$200<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(Details!\$E\$2:Details!\$E\$200<>"A",ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))),"")

But now I need to add an OR to it. I've tried this:
=IFERROR(IF(INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(OR(Details!\$E\$2:Details!\$E\$200<>"A",Details!\$E\$2:Details!\$E\$200<>"X"),ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(OR(Details!\$E\$2:Details!\$E\$200<>"A",Details!\$E\$2:Details!\$E\$200<>"X"),ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))),"")

And this:
=IFERROR(IF(INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(OR(Details!\$E\$2:Details!\$E\$200<>{"A","X"}),ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(OR(Details!\$E\$2:Details!\$E\$200<>{"A","X"}),ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))),"")

And this:
=IFERROR(IF(INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(OR(Details!\$E\$2:Details!\$E\$200={"B","C"}),ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))=", "," ",INDEX(Details!\$B\$2:\$B\$200&", "&Details!\$C\$2:\$C\$200,SMALL(IF(OR(Details!\$E\$2:Details!\$E\$200={"B","C"}),ROW(Details!\$E\$2:Details!\$E\$200)-1,""),ROW(1:1)))),"")

And nothing works. It still gives me all 4 instead of only the two I need.

9. Re: Get Info from cells using if, index, small and concatenate

Try .....

=IFERROR(IF(INDEX(DETAILS!\$B\$2:\$B\$200&", "&DETAILS!\$C\$2:\$C\$200,SMALL(IF((DETAILS!\$E\$2:DETAILS!\$E\$200<>"A")+(DETAILS!\$E\$2:DETAILS!\$E\$200<>"X"),ROW(DETAILS!\$E\$2:DETAILS!\$E\$200)-1,""),ROW(1:1)))=", "," ",INDEX(DETAILS!\$B\$2:\$B\$200&", "&DETAILS!\$C\$2:\$C\$200,SMALL(IF((DETAILS!\$E\$2:DETAILS!\$E\$200<>"A")+(DETAILS!\$E\$2:DETAILS!\$E\$200<>"X"),ROW(DETAILS!\$E\$2:DETAILS!\$E\$200)-1,""),ROW(1:1)))),"")

As requested earlier it would help if you could post a sample file.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1