3 rows of 28 not working using index/match

[FreakyGirl]

hello.

I have a worksheet consisting of almost 100 columns but currently only 29 rows.

starting from the first column it breaks down a very long string into workable names & numbers.

it works great - except rows 16, 18 & 27 in the last 8 columns (CN to CL) which are not returning the right numbers.

since all the rows above & below work properly I cant find the problem.


columns BT to CI is all the extracted info from the larger string & is correct. (shown in purple)

the info in those columns need to be put in a different order based by the 1 of 4 letters (B, G, R, Y) found throughout the BT-CI matrix

columns CJ to CM are the positions of those 4 letters & will only be 1, 5, 9 or 13. (shown in gray)

CN to CL is the info in the proper order using IF, & index/match. (shown in white)


as stated all the other rows are correct except for the 3 mentioned.


for example:
CO16 the number showing is 2. the correct number should be 1 as shown in CH16.

the logic for CO16 is as follows:
look in CJ16 & if the number is 1 then index/match BV/BU for the return value, if the number is 5 then index/match BZ/BY, if the number is 9 then index/match CD/CC for the return value, if the number is 13 then index/match CH/CG.

=IF(CJ16=1,INDEX($BV$2:$BV$52,MATCH(BU16,$BU$2:$BU$52,0)),IF(CJ16=5,INDEX($BZ$2:$BZ$52,MATCH(BY16,$BY$2:$BY$52,0)),IF(CJ16=9,INDEX($CD$2:$CD$52,MATCH(CC16,$CC$2:$CC$52,0)),IF(CJ16=13,INDEX($CH$2:$CH$52,MATCH(CG16,$CG$2:$CG$52,0))))))

also, on the same row, CS16 is also wrong. the number showing is 2. the correct number should be 1 as shown in CD16.

how can it be wrong when the other rows surrounding it are correct & formulas were dragged down?

thanks in advance for any help.
FreakyGirl

 

[NARAYANK991]

Hi ,

I am not sure what you want to do.

Every column of names BU , BY , CC and CG has duplicates , and in some cases , the duplicates have different numbers associated with them in columns BV , BZ , CD and CH.

Thus CN16 has the name andymae61 ; since CJ16 has the value 13 , the lookup will be done in column CG ; CG6 and CG16 have the name andymae61 , but the values in CH6 and CH16 are 2 and 1.

The same is the case in quite a few other cases.

Narayan

 

[FreakyGirl]

hello
your a GENIUS! that is exactly what's going on. the index/match is finding the 1st occurrence of the name & returning the number that's next to it. i never thought to look in the data for the problem.

to make it easier to see the duplicate names I added in 4 countif columns (CY to DB) & put a conditional on the other 4 columns so they are highlighted.

since there is no limit to the number of times a name can show in any 1 column, maybe doing each row rather than a column would be better suited since there wont be any dups at all.

now that I know the issue i'm going to play around with this a bit & see if I cant figure out a solution.

however, I may be back for help. who am I kidding lol - i'm sure ill be back for help

thanks so far,
FreakyGirl