Lookup nth occurrence

[Blake 7]

Hi All,

Thanks for looking.

Please see the attachment which describes the issue and demonstrates the desired result.

I am looking for a formula to go in H2:P2 of sheet 1 which looksup the value in A2 Sheet 1 and returns a location from Data Sheet I2:I10.

There are some 60k rows of data - some records will have 1 location and others more.

many thanks for your help.

Any questions please ask

[Melvinrobb]

Put the following formula in H2 and drag across:

=INDEX('Data Sheet'!$I$2:$I$10,MATCH(Sheet1!$A2&RIGHT(Sheet1!H$1,2),'Data Sheet'!$A$2:$A$10,0),1)

[Blake 7]

many thanks - top bloke, thats really helpful.

You have really saved me some valuable time. Awesome

[JieJenn]

I am gonna assume you will have multiple member IDs.

In H2, enter this formula with Ctrl + Shift + Enter keys

=INDEX('Data Sheet'!$I$2:$I$19,MATCH($A2&VALUE(TRIM(SUBSTITUTE(H$1,"Location",""))),'Data Sheet'!$B$2:$B$19&'Data Sheet'!$N$2:$N$19,0))

[Blake 7]

JJ - thanks for your help. Your solution worked perfectly and yes, your assumption was correct, thousands of member id's!!!

all the best!