Multiple column lookup

[badgerboy13]

Hi
I am trying to create an anonymised list of clients and activities in a community group that monitors funding sources and client attendance over time.

I have two sheets:
ClientData contains full un-anonymised data from clients – name, contact details, membership no , AttendanceNumber and activity .
Lookup has a list of activities with a corresponding 4 digit code for each activity’s funding source, code for Attendance1, Attendance2+ and Other (used for activities that aren't funded externally or that aren't used for reporting back to funders)

I wish to create a third sheet that shows just the client membership number and the code that corresponds to their activity. I would normally just use index/match or a simple lookup (I have done this for the funding source) but am getting stuck with the attendance number aspect. I need the formula to look up a client ID and if it is their 1st attendance, look up the code in the Lookup/Attendance1 column. If it is not a first attendance (ie ClientData/Attendance is >1), look in the Attendance2+ column and then, if either of those columns are empty, return the corresponding value from the "other" column and return this value

I hope I have explained this properly! Thanks in advance for any help
J

[BadlySpelledBuoy]

Hi J,

Might be easier to provide a solution if we could see it in context. Attach a sample workbook showing your data layout and expected results.

To attach a sample workbook click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

BSB

[badgerboy13]

Hi BSB, thanks, I had tried when I first posted but got a message that I couldn't add attachments until I'd posted some more. Now attached.
J

[kvsrinivasamurthy]

Pl upload a sample file giveing some examples in the file with explanation hoe you are getting result.

[Glenn Kennedy]

To return the ID numbers, sorted in umerical order, an array formula:

=IFERROR(INDEX(ClientData!$C$2:$C$19,MATCH(0,COUNTIF(ClientData!$C$2:$C$19,"<"&ClientData!$C$2:$C$19)-SUM(COUNTIF(ClientData!$C$2:$C$19,"="&A$1:A1)),0)),"")

To return the Activity Code, a bit of a monster:

Please Login or Register  to view this content.

What is the Referral Code? It might be possible to simplify this considerably, depending on yur answer to that Q....

[Glenn Kennedy]

I forgot to add:

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[badgerboy13]

Thanks Glenn, that works exactly how I want it too.
Thank you so much for your help
J

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.