Excel formula to create new column of codes 1

[srogers]

I have 4 columns with about 13,000 rows.

Col A Col B Col AK Col AN
10020 122.4 10020
10020 26 112 10021
10021 24.5 10022
10021 26 12.94 10023
10022 45.6 10024
J0270
K1254


Columns A, B and AN are codes and column AK is fees.
The codes in columns A and AN have the same number of digits and are both sorted in ascending order.
Column AN has a longer list of codes in it than column A.

I want to make a new column of codes that lists any code that exists in AN as well as in A. If there is a code in column B then I want to ignore the code in column A.

So from the example above I would want these codes in my new column:
10020
10021
10022

Then, next to my new column, I want another column that shows the corresponding fee. So the resulting two new columns would be:

AQ AR
10020 122.40
10021 24.50
10022 45.60


The formula I have in AQ is:
=IF(B2150="",(IF(MATCH(AN2150,$A$2150:$A$11686,0),AN2150,"")),"")

This works most of the time but in spot checking I’ve found a couple of codes not in my new column AQ even though they are in both A and AN.

Is there a flaw in this formula or is there another formula I should be using instead?

Thank you.

 

[sawedoff]

Your =IF(B2150="" is what is causing the problem. For instance, on the top line of your example, the cell in B that is on the same line as 10021 has a 26 in it, and therefore prevents the 10021 from being matched. You will have to do a comparison with column A and B before doing the match, in order to weed out the entries in A that have a code next to them. One easy way would be to put in a spare, hidden column (maybe AR) that has a formula that puts the code from A in only if B is blank., then do your match with AN and AR.

Sawedoff

 

[sawedoff]

In a hidden column (I'm using AZ) put
=IF(B2150="",A2150,"")

Then in column AQ, put
=IF(MATCH(D2150,$AZ$2150:$AZ$11686,0),D2150,"")

In column AR put
=LOOKUP(AQ2150,$AN$2150:$AN$11686,$AK$2150:$AK$11686)

These formulas should put the code in AQ, the fee in AR and you can leave AZ hidden.

Sawedoff

 

[srogers]

Thanks for your reply.
That worked - I just had to change a couple of things.

This didn't work for AR:
=LOOKUP(AQ2150,$AN$2150:$AN$11686,$AK$2150:$AK$11686)
because I think that's a one row lookup. The columns don't line up so it needs to be able to look in the whole column insead of on the same row.
So I used this instead (range named the AN and AO columns)
=VLOOKUP(AQ2150,UHC,2,False)

And for anyone else using this as an example the column D in this formula:
=IF(MATCH(D2150,$AZ$2150:$AZ$11686,0),D2150,"")
is really AN.

Thanks again!!