I have a bit of a problem. I am looking to develop a model that produces a unique code based on two criteria which are input in cells A2 and B2. First the Course code is input, Second the business area. The formula I am looking for will look at these two items and produce a unique code, specific to the Business Area.
For example
Code Business Area Unique Code
CC002 MVT 700065
The formula is in the unique code cell (C2) . I have a list with all three items in it Starting with Corse Code then Business Area and finally the Unique Code. The list Array name is SIO.
If this problem were the other way round it would be a complete dodle with a lookup storming in to save the day but the team only has the first two peices of information and has to seaerch through 2500 items for the third.
This is the Code I have tried but failed with
=INDEX(B3,MATCH(A3,SIO,4))
Perhaps 2 lookups would do the job? I don't know.
Any help would be much appreciated.
Marcus
Marcus Small
Related resources
Replies (3)
Please login or register to join the discussion.
More options
Not sure if I've understood this right, but it sounds like you have every combination of Course Code & Business Area in SIO, together with the corresponding Unique Code.
If so, it sounds like the simplest way to extract the required Unique Code is by Autofilter - let users pull the first two fields from the drop-down lists and the required record is displayed.
If you require input & results in a separate area, then an advanced filter would do the job. The input cells (plus labels above) become the criteria range - you'd need to put the output somewhere else.
Another option: Input cells are the criteria range as above, but the required Unique Code is extracted using the DGET function - have a look in help.
Wow, DGET!
Stephen Foster mentions DGET as an aside, but this looks like the perfect answer to me, and I would guess it makes Excel work less hard than the concatenation and array formula solution.
The D... formulae are not things I've ever played with before but I'm glad I did and many thanks to Stephen.
The explanation in Excel Help is not the best, however, and it's easier to look at an example, so in case it's useful to others I've posted one at:
http://www.bobfoley.co.uk/aweb_answers.htm
It's called:
Using_DGET_for_lookups_with ultiple_criteria.xls
matching two items
I am sure someone will come up with a better way, but could you do it by concatenating the Code and business area within the lookup, and adding a column to the left of 'SIO' also with the concatenated code and business area? The lookup would then look like:
=VLOOKUP(A2&B2,sio,3,FALSE)
Simon