Excel Function to return position from array

If you want only the first occurrence of Andrew then you can use

=index(C2:C1000,3,1)

If you want the sum of all occurrences of Andrew then you can use

=sumif(A2:A1000,"Andrew",C2:C1000)

@MrDavidThorn

So you want the value from the last item in the group?

David         Eric White Ltd
Andrew        Marks
Peter         Do it all

Select allOpen in new window

Is the entire list ordered the way your example illustrates?
Can the list safely be sorted?

The problem is that by using 3 in the index function Is assuming that I already know the row posistion of the first entry,  - the example above is not the data set I have as its senstive data and I can not publish it.

I want to enter the posistion number so I need a function something like an IndexIf.

If I understand you correctly you want the last customer that Andrew sold to? You can do that without counting "Andrew"s. Try

=LOOKUP(2,1/(A1:A100="Andrew"),B1:B100)

regards, barry

To get the number 3 you can use the formula

=match("Andrew",A:A,0)

an index into the entire list?

Sorry that should have been

=match("Andrew",A2:A1000,0)

and the entire formula would be

=index(C2:C1000,match("Andrew",A2:A1000,0),1)

=match("Andrew",A2:A1000,0) returns the first match found in the list, what if I want to return the 2nd or 3rd?

If the repetitions are consecutive then you can have

=match("Andrew",A2:A1000,0)+1
for the second one

=match("Andrew",A2:A1000,0)+2
for the third one

Just to reclarify - I have this data set

Column A  Column B           Column C
Sales          Customer            Sales
David         Frank And Co        232
David         Eric White Ltd       1210
Andrew      WhiteSpace ltd      422
Andrew      Richards PLC         11
Andrew      Marks                    432
Peter          Dillions                 121becuase
Peter          Do it all                 333

I know Andrew Occurs three times in Column A, I want a function that returns the word Marks from Coloumn B based upon me passing the number 3 (not the row) to the function. If I was to pass the number 2 I would expect Richards PLC  

Although the array is the entire dataset, I want to filiter out all entries of Andrew and return column B based upon a passed posistion into the array.

=index(B2:B1000,match("Andrew",A2:A1000,0)+F1-1,1)

will return the customer name

Where F1 should contain the occurrence number.

Or maybe

=index(B2:B1000,match(E1,A2:A1000,0)+F1-1,1)
to return the customer name

and
=index(C2:C1000,match(E1,A2:A1000,0)+F1-1,1)
to return the sales vale

Where
E1 should contain the sales's name
F1 should contain the occurrence number.

@MrDavidThorn

Is the list sorted?  If not, can the list be sorted?

Sorry the list isnt sorted and unfortunatley I cant sort it, so I cant use  - =index(B2:B1000,match(E1,A2:A1000,0)+F1-1,1)

In that case you should go ahead with the first option given by barryhoudini in comment #37574829

Here's a demo of that formula attached

Revised formula to this in H2

=INDEX(B$2:B$100,SMALL(IF(A$2:A$100=F2,ROW(A$2:A$100)-ROW(A$2)+1),G2))

confirmed with CTRL+SHIFT+ENTER and copied down

Where F2 is salesman and G2 instance number. You get an error if that instance doesn't exist. Error can be eliminated by adding to the formula if required - which version of excel are you using?

regards, barry
Instance-number.xls

Barry's original formula works with unsorted data.

Using this configuration of cells:

Sales	Customer       	Sales
Peter	Do it all      	333
David	Frank And Co	232
Andrew	WhiteSpace ltd	422
David	Eric White Ltd	1210
Andrew	Marks         	432
Peter	Dillions       	121
Andrew	Richards PLC  	11

Select allOpen in new window

=LOOKUP(2,1/(A1:A100="Andrew"),B1:B100) produces Richards PLC
=LOOKUP(2,1/(A2:A101="Peter"),B2:B101) produces Dillions

@Barry

How does the "1/()" in your formula work?  I'm unfamiliar with the notation.  I'm sure it's slick.

That's right aikimark - that would be the recommended approach for finding the last instance, I think. The other version I suggested will work for any numbered instance

regards, barry

>>that would be the recommended approach for finding the last instance

That is why I was asking so many questions.  It seemed that there was a concentration upon a number, rather than the LAST nature of the data retrieval problem.

@MrDavidThorn

I thought you wanted the last instance of the value.  Please clarify.

Hello aikimark, the "test", i.e.

(A1:A100="Andrew")

produces an array of TRUE/FALSE values. When you divide 1 by that array you get an array of either 1s (from TRUE) or #DIV/0! errors (from FALSE).

If you use 2 as the lookup value against such an array it matches with the last numeric value in that array, which will correspond to the last TRUE from the test, i.e. the last row where the salesman name was matched.

It's a little bit of a "cheat" in some respects. LOOKUP should be used on a sorted lookup array......but if it isn't sorted and the lookup value is greater than any value in the lookup array it turns out that it matches with the last numeric value, which is the same principle as this formula

=LOOKUP(9.99E+307,A:A)

used to return the last numeric value in column A

regards, barry

it may be a cheat, but it is still quite slick.  Thanks.