Stuck on an isna match index problem

**envelope pusher** · 09-02-2015, 03:57 PM

Okay, index, match, and isna are all new to me, but I'm familiar with the most common functions, and have done stuff like nested if functions, but not much more complex than that. I can usually look at a program, or function and figure out how to get what I want, but I'm stuck on this one, as I've not been able to find any equivalent functions to borrow from. I'm able to match and sort two columns satisfactorily

=IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0)))

What I need to do is this, compare two tables and output the matches of two individual columns:

A B C D L M N
first last data data first last data

I need to match the first and last names (A and B) and (L and M) and sort the data from L, M, and N, so that the order matches by name in A, B, C, and D. I've tried to figure this out myself, but haven't had any luck.

Any hints or tips are appreciated in advance.

Thanks,
ep

**FDibbins** · 09-02-2015, 04:12 PM

Hi, welcome to the forum

1st, you dont really need to use ISNA, 2010 has a nifty formula for testing for errors...
=IFerror(INDEX(C:C,MATCH(A1,C:C,0)),"")

2nd, it would probably be easier to assist if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**TMS** · 09-02-2015, 04:44 PM

If you MATCH A1 against column C, and then INDEX column C, you'll just get whatever is in A1 or a blank (null) returned.

You might as well use:

Formula:

Please Login or Register  to view this content.

Regards, TMS

**envelope pusher** · 09-02-2015, 04:51 PM

I need to compare the first two tables, matching first and last name. Output should be third table, which is data from Table 2 sorted by match.

Tester 9-2-2015.xlsx

**envelope pusher** · 09-03-2015, 12:41 PM

I've tried both suggestions. I think my original function above is incorrect as a starting point.

**ChemistB** · 09-03-2015, 01:15 PM

To get exactly the table you have in your workbook in N:Q (I put mine in S:V), I used ARRAYED formulas and two different ones (one for matching names and one for names only appearing in table2

In N2 copied across and down, this ARRAYED formula
=IFERROR(INDEX(H$2:H$32, MATCH($A2&$B2, $H$2:$H$32&$I$2:$I$32,0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

In N31 copied across and down, this Arrayed Formula
=INDEX(H$2:H$32, SMALL(IF(ISERROR(MATCH($H$2:$H$32&$I$2:$I$32, $A$2:$A$29&$B$2:$B$29,0)), ROW($H$2:$H$32)-ROW($H$2)+1),ROWS($A$1:$A1)))

Is that what you want?