LinkBack URL
About LinkBacks
Why is this an array formula?
Formula:
Please Login or Register to view this content.
Why is this an array formula?
Formula:Please Login or Register to view this content.
Because in order to interprete Makes&Models, Excel has to first combine each member within Makes and Models to each other. This requires it allocate extra space to memory. If you want to do it without using an "Array," try
=INDEX(Code, MATCH(Make&Model,INDEX(Makes&Models,),0))
Does that make sense?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
MATCH normally operates on a single range, but in your case you want to match both on the Make and on the Model. Consequently, each element of the two ranges Makes and Models need to be combined together into another range, and so it is that operation which makes this into an array formula.
Hope this helps.
Pete
It is just because of the concatenation of Make&Model. But it can be done without using Ctrl+Shift+Enter
Formula:Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Its because of the concatenate (&).You want to keep the "Pairs" of the make and model together.
or another version non array formula
=INDEX(Code,MATCH(1,INDEX((Make=Makes) *(Model=Models),0,0),0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks