ISNA INDEX Match Formula help

Try:

=INDEX($D$3:$D$46,MATCH(B3,$G$3:$G$46,0))

copied down.

To return blanks when no match found.

=IF(ISNUMBER(MATCH(B3,$G$3:$G$46,0)),INDEX($D$3:$D$46,MATCH(B3,$G$3:$G$46,0)),"")

copied down

tcandls,

Welcome to the forum!
Something like this should work for you:

Please Login or Register  to view this content.

It should be

=IF(ISERROR(INDEX($B$3:$B$46,MATCH($G$3:$G$46,D3,0))),"",INDEX($B$3:$B$46,MATCH($G$3:$G$46,D3,0)))

My formulas are not case sensitive.... so ERNESTINE CANCINO should match fine with Ernestine Cancino.

Cutter, although I never tested it myself, I learned a long time ago in my days at another popular forum, that the IsnumberI(Match()) is actually more efficient than the COUNTIF() alternative.... that's one reason I always go with the ISNUMBER(MATCH())

Originally Posted by tigeravatar

tcandls,

Welcome to the forum!
Something like this should work for you:

Please Login or Register  to view this content.

Thanks for the welcome. I've copied this formula, and it is quite similar to what I used in past however, I keep getting a ref error. I'm actually using excel 2007 and am wondering if formula is different for this version of .xls.

It's not even returning the value in column.

here's sample data and forumula I copied: =IF(ISNA(MATCH(D2:$D$7,$G$3:$G$13,0)),"9",INDEX(A2,MATCH(D2,$G$3:$G$13,0)))

USERID FIRST_NAME LAST_NAME Full_Name Match Function UserLog Distinct 2012 Only
111111 i i Fela Navarro 9 Anna Alva
222222 i i Arnold Doe 9 James Doe
333333 i i James Doe 9 Arnold Doe
444444 i i No One #REF! No One
555555 5 5 James Paul 9 Paul James
666666 6 6 TINA CAN #REF! Tina Can
777777 7 7 Anna Alva 9


Much thanks to all for helping me get back into the groove of things...I've been away from this for some time.

tcandls,

In the formula you posted I can see multiple errors with its syntax. And I am unable to create a table faithful to the one you've posted and I don't know what results you are expecting from the formula, so if you could attach a sample workbook that would be very helpful.

As for the formula syntax, you have:
=IF(ISNA(MATCH(D2:$D$7,$G$3:$G$13,0)),"9",INDEX(A2,MATCH(D2,$G$3:$G$13,0)))

The first red part is bad because when you're performing a match, you should only be looking up a single value, instead of a range of a values.
The second red part is bad because index should be looking at the column of return values, not a single cell.

Correcting your formula would have it look like this:
=IF(ISNA(MATCH(D2,$G$3:$G$13,0)),"9",INDEX($A$3:$A$13,MATCH(D2,$G$3:$G$13,0)))


But I can't confirm it will provide the desired results because I am unable to faithfully recreate your test table.

Originally Posted by NBVC

Cutter, although I never tested it myself, I learned a long time ago in my days at another popular forum, that the IsnumberI(Match()) is actually more efficient than the COUNTIF() alternative.... that's one reason I always go with the ISNUMBER(MATCH())

Thanks for that. I can see that being the case with large ranges since the MATCH() would only go as far down as needed (if there was a match) whereas the COUNTIF() always looks at the entire range (as would the MATCH() if the match was the last entry) but the OP in this case is only looking at 43 rows. I will definitely keep that in mind from now on though. So, thanks again for the tip.

I found this discussion on it (between Domenic and Harlan Grove) from 2005 :
http://www.excelforum.com/excel-work...umproduct.html