Find the last-row result of multiple matches?

**goodiein2808** · 07-08-2011, 05:26 AM

Now I've tried a couple of methods but none yeiled desirable result so far.

I want to search "Jay Abrams" from sheet1, file A in colum F of sheet1,file B, now, there are more than 1 match in colum F sheet1,file B. I want the result at the last position, or in other words, data with the max row number.

I've tried lookup, but the resulf wasn't correct.

Now I'm using index, something like this:

index('[sheet1fileB.xls]Sheet1'!$A$2:$A$3000,LARGE(IF('[sheet1fileB.xls.xls]Sheet1'!$F$2:$F$3000=B3,ROW(1:3000)),1))

STILL, the result isn't correct. There was no grammar mistake, above command is written here for demostration purpose only, so I need you people to do me a farvor:

1, tell me if lookup func is the right path to go, and tell me how to compose the correct command line

2, if lookup won't work, please tell me if there is some LOGIC flaws reflected in my INDEX sentence posted above? For example, I want to realize the purpose A, but my line took me to B. And again, tell me how to compose/modify it.

Thanks a lot!

**DonkeyOte** · 07-08-2011, 06:43 AM

The approach you have used is not wrong per se though:

a) it does require array entry (ie must be confirmed with CTRL + SHIFT + ENTER and not just Enter per "normal" formulae)

b) You should really adjust

ROW(1:3000)

to either

ROW($1:$2999)

or preferably

ROW($A$2:$A$3000)-ROW($A$2)+1

Another alternative approach would be:

=LOOKUP(2,1/('[sheet1fileB.xls.xls]Sheet1'!$F$2:$F$3000=B3),'[sheet1fileB.xls.xls]Sheet1'!$A$2:$A$3000)
confirmed with Enter

Note that even though the above does not require Array entry it is not very efficient (either)

**snb** · 07-08-2011, 07:10 AM

or arrayformula ?

PHP Code:


=INDEX('[fileB.xls]Sheet1'!$F$2:$G$3000,MAX(IF('[fileB.xls]Sheet1'!$F$2:$F$3000=B3,ROW($F$2:$F$3000)-1,0)),2)

**DonkeyOte** · 07-08-2011, 07:58 AM

@snb, yes, I think that is similar to OP's original formula (MAX rather than LARGE), however, you need to adjust either your use of ROW or initial INDEX range - as is you are offsetting by 1 row.

**snb** · 07-08-2011, 08:51 AM

@DO

Sure !
I amended the code. (I overlooked that because I tested on my system using F1:F3000).

**daddylonglegs** · 07-08-2011, 11:13 AM

Originally Posted by DonkeyOte

@snb, yes, I think that is similar to OP's original formula (MAX rather than LARGE),.......

There is a difference, though, if there are no rows that meet the criteria. MAX will return zero and LARGE will return an error. The former is a problem because when the zero is fed to the INDEX function the whole column is returned. Here because only a single value can be returned by the formula that manifests itself as the contents of G2 - hence an erroneous result.......better to get the error with LARGE.......

**snb** · 07-08-2011, 11:53 AM

wouldn't this suffice ?

PHP Code:


=if(countif('[fileB.xls]Sheet1'!$F$2:$F$3000,B3)=0,"",INDEX('[fileB.xls]Sheet1'!$F$2:$G$3000,MAX(IF('[fileB.xls]Sheet1'!$F$2:$F$3000=B3,ROW($F$2:$F$3000)-1,0)),2))