Excel VLookUp But Only Once

Question

I have an Excel spreadsheet with one tab of our inventory of cars showing their make, model and serial number. I also have another tab showing our customer orders for cars but they only contain make and model. I want to match the custom orders to the inventory but I need them to only match once per serial number.

For instance, I have three customers wanting Honda Civics. I only have one on hand. Currently, VLookUp will match each customer to the same one so it appears as if I can fulfill all three orders; which of course I can't. I only have one.

I believe I am going to need something more flexible than VLookUp.

I am hoping that I can find a formula smart enough to match orders to inventory only once, then to ignore that match when moving onto the next order. This way I know exactly how many I can actually fulfill.

Answer 1

Here's an array-formula requiring the following structure:

Make & Model are combined into a single cell for both customer requests and your stock.

Your stock has named ranges for its 2 columns: MakeModel and Serial. The ranges can include headings, but they MUST both start in row 1.

Your customer requests start in A2.

Enter this array-formula in B2 only:

=IFERROR(INDEX(Serial,SMALL(IF(MakeModel=A2,ROW(MakeModel)),COUNTIF($A$2:A2,A2))),"Insufficient Stock")

To enter an array-formula, you need to press CTRL+SHIFT+ENTER. After entering the array formula, then fill it down.