Help needed please : Align duplicates from two columns to same rows in Excel

[MariusSKG]

Hi there,

I am in need of help, please.

I have found a formula which helps me align the same data in two separate columns which works well: (=IF(ISNA(MATCH(D2,F:F,0)),"",INDEX(F:F,MATCH(D2,F:F,0)))

Although in addition to aligning the data from the two columns, I need to know how I use the same formula or what different formula to use to also take with it information in an adjacent cell when it aligns the 'primary data'. In the attached example, I am wanting data from column F to align with the data in column D, whilst 'taking with it' and adjacent information which is in column G. The attached example on shows what I have been able to achieve with the formula I already have.

I would truly appreciate any assistance in this regard.

Thanking everyone in advance for taking the time to help me here.

All the best!

[#2]

@MariusSKG Always a bit tricky to give recommendations based on a picture alone, but I'll give it a try.

First, you can simplify your formulae by using IFERROR. Then you can eliminate the first part where you check if there is a match. 

Second, you use index on both columns you want to return values from, i.e. F:G and you match the value in D (same row) to values in column F:F (this gives you the row number). Add 1 if you want to return the value from column F and 2 for column G.

=IFERROR(INDEX(F:G,MATCH($D2,F:F,0),1),"") 

=IFERROR(INDEX(F:G,MATCH($D2,F:F,0),2),"")

[#3]

@Riny_van_Eekelen Hi Riny, THANK YOU for the advice. Would the attached help? If you were able to PLEASE enter the formulas where they need to go so that I could simply copy/paste them as needed, I would be eternally grateful. I am hoping you can help me, I have been struggling to find someone to help me with this for a while now.

[#4]

@MariusSKG Looking at your schedule I must admit that I'm not entirely sure why you also want to match the SKU number. You column D has all the SKU's you want (but so many blank rows?) and you could easily find the cost from your original column G alone, by indexing column G, based on a match in column F.

But again, I can't really judge what you will do with this next, so I inserted a column to make some space. Sorted column D to get rid of the blanks. Column E contains the matched SKU number and column F the matched cost. 

[#5]

Riny! I figured it out using what you gave me!!!!! THANK YOU! THANK YOU! THANK YOU!

[#6 · Accepted solution]

@MariusSKG You're welcome!