exceldata-managementexcel-match

Copy values from cells from one sheet to another with MATCH and INDEX

Sheet 1:

enter image description here

Sheet 2:

enter image description here

Nature of data: Taxon names on sheet 1 & 2 are not ordered in the same manner, and the taxon names on sheet 1 is a subset of taxa names of sheet 2.

What I want done: I want to copy the values linked to taxa from columns C through L in sheet 2 to the same taxa in sheet 1 in the columns K through T.

So, if I have a "Hydra viridissima (Sheet 2, A6) somewhere in sheet 1, it should bring the values "5" (Sheet 2, J6 under "pre") and "5" (Sheet 2, L6 under "oth") to their respective cells in Sheet 1, which in this case would be in columns R and T.

What I have tried: Went through a tutorial and thought I had it with:

=INDEX(IndexArea,MATCH(B2,Taxon,0),MATCH($K$1,FeedingType,0))

It's not working, though it does identify when things are not matching exactly (#N/B).

I tried to add sheet2! in the formula in reference to names assigned to the ranges like this: =INDEX(Sheet2!IndexArea,MATCH(B2,Sheet2!Taxon,0),MATCH($K$1,Sheet2!FeedingType,0)), but gives the same error.

Can anyone help me out?

Thanks!

Solution

  • Your formula treats Taxon, FeedingType and IndexArea as defined names but your narrative doesn't mention them being assigned defined names. Try it as row and column references.

    ' in US-EN
=INDEX(Sheet2!$B:$M, MATCH($B2, Sheet2!$A:$A, 0), MATCH(K$1, Sheet2!$B$3:$M$3, 0))
' should the *FeedingType* come from column J?
=INDEX(Sheet2!$B:$M, MATCH($B2, Sheet2!$A:$A, 0), MATCH($J2, Sheet2!$B$3:$M$3, 0))