# looking at a row from right to left and finding matches to list

1. ## Re: looking at a row from right to left and finding matches to list

Maybe i spoke to soon, it worked when i put it into the example but moving it to my actual file and changing cells in the formula my results are coming up 3 cells to the right as the dept names not numbers. For the result in E7 im getting H2 there.

Important list is AQ3:AQ135
levels are F3:O3
Results fall into AI:AK

2. ## Re: looking at a row from right to left and finding matches to list

Hi and welcome
although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.

3. ## Re: looking at a row from right to left and finding matches to list

Assuming that the data, rather than the headers, startin row 3 in all cases...

=IFERROR(INDEX(\$F3:\$O3,SMALL(IF(ISNUMBER(SEARCH(\$AQ\$3:\$AQ\$5,\$F3:\$O3)),COLUMN(\$F3:\$O3)-COLUMN(\$F3)+1),COUNT(IF(\$F3:\$O3>0,IF(ISERROR(MATCH(\$F3:\$O3,\$AQ\$3:\$AQ\$135,0)),"",\$F3:\$O3)))-(COLUMNS(AI:\$AK)-1))),"")

array-entered.

@drocket

5. ## Re: looking at a row from right to left and finding matches to list

Row1 is blank

For the first \$AQ:\$5 is that supposed to be \$135?

Something seems to be going funky now, it references column F a lot and seems to pick up any first number instead of numbers only in the list. If there are 2 results most of the time it puts column F for both. Even changing the \$5 to \$135 does the same thing.

6. ## Re: looking at a row from right to left and finding matches to list

Yes it is. See sheet. Are there blanks in the list of important departments?

7. ## Re: looking at a row from right to left and finding matches to list

There are blanks figured future expansion, data is really \$AQ\$3:\$AQ\$89 I just changed that and sent the example file to my work computer and put all data into the example file. Same results, I'm also seeing things getting flipped where something that was supposed to be in AI is in AK but AI and AJ show the repeating dept. I'm also looking for spaces in the cells, im sure its something I'm doing or working off of. Would cell data type matter like changing from general to number, etc.

Let me look at this a little more and see if there is a common theme to what's going on.

8. ## Re: looking at a row from right to left and finding matches to list

What im seeing is something doesn't work with the COLUMN(\$F3:\$O3)-COLUMN(\$F3)+1 this seems to be static and doesn't work when multiple depts are present and out of order. I was able to replicate what I am seeing attached in blue. I think my example made it look like numbers will always be in order. Thank you for all your time and help.

I can also get this to a pivot with more steps just need to consolidate it into 3 column removing blanks in the rows.

9. ## Re: looking at a row from right to left and finding matches to list

Forgive me for saying this, but we are roughly twenty hours into this thread and now you are telling us that the sample data does not properly resemble the real data - that there will be gaps in the important departments list and that your departments may be out of order. Members invest a significant amount of their free time trying to help, but can only base their solutions on what they know that you have told them.

Does the latest set of sample data properly reflect all eventualities?

10. ## Re: looking at a row from right to left and finding matches to list

Yes, I wasn't sure how this would turn out or how to describe it based on what would be a solution, didn't know being out of order and blanks at the end of the list would effect formula as original formula didn't have this.

11. ## Re: looking at a row from right to left and finding matches to list

I know what the problem is... but I'm a bit frazzled now... and need to eat and have a beer. I'll think about this and get back to you...

12. ## Re: looking at a row from right to left and finding matches to list

Thank you, there is no rush on this, I had free time to work on automating this file that i just had to take over.

13. ## Re: looking at a row from right to left and finding matches to list

One last go before I log out...

=IFERROR(INDEX(\$F3:\$O3,SMALL(IF(ISNUMBER(SEARCH(\$AQ\$3:\$AQ\$135&" ",\$F3:\$O3&" ")),COLUMN(\$F3:\$O3)-COLUMN(\$F3)+1),COUNT(IF(\$F3:\$O3>0,IF(ISERROR(MATCH(\$F3:\$O3,\$AQ\$3:\$AQ\$135,0)),"",\$F3:\$O3)))-(COLUMNS(AI:\$AK)-1))),"")

14. ## Re: looking at a row from right to left and finding matches to list

Same results, is there a way to put in (not equal to) <>\$AK3 for column AJ and <>\$AK3&\$AJ3. The only constant in this is that the result for AJ will always be left of where AK was found and AI will be left of where AJ & AK are found. Why looking right to left is constant.

15. ## Re: looking at a row from right to left and finding matches to list

I cut your sample down a bit, stopping the keywords at row 10 - for ease of testing. However, to future-proof it, I replaced the fixed range with a Named range (called Import... CTRL-F3 to view it), which will expand as you add numbers -currently out to cell AQ500 - to your list of important sites.

=Sheet1!\$AQ\$3:INDEX(Sheet1!\$AQ\$3:\$AQ\$500,COUNTA(Sheet1!\$AQ\$3:\$AQ\$500))

The problem was that I had used SEARCH in the formula. So when I searched for 64 it would find 64, 640, 264, etc, etc. So I replaced it with a requirement to look for an exact match. Seems OK, now. Check it out. I'll be in-and-out of the forum from time to time today. I'm brewing beer today... 08:00 and I'm already covered with barley dust... much more productive than the EF...

Page 2 of 3 First 1 2 3 Last

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