# Excel Lookup Formula Needed

I have a worksheet, similar to the one in this image; I need a formula that will search the 'Make' and 'Color' columns in the table and if one of the pairs of values matches a pair in the outside list, the corresponding 'License Plate #' value will be added to the outside list. Essentially I'm looking for something along the lines of IF 'Nissan' exists in column O AND the value in column P next to it equals 'Red', THEN cell Q5 will equal the contents of cell N4 or 'PRT-2417'.

• What happens if there's more than one Green Chevy? – Alex M Apr 5 '19 at 17:55
• The actual data that I'm using is patient names and ID numbers so both sets are all unique instances that only appear once. – d_iggs17 Apr 5 '19 at 17:56
• Got it. Well done. You might add that to the question; the fact that the data pairs are known to be unique is relevant. – Alex M Apr 5 '19 at 17:57
• – Scott Craner Apr 5 '19 at 17:57

Your issue can be solved by using `Index` and `Match` combination. How it works:

• I'm assuming that you have Source data in Range `A2:C6`(excluding header).
• Enter Target data in Range `E1:F6` (including header).
• Enter this Array (CSE) Formula in cell `D2`, finish with Ctrl+Shift+Enter and fill down.

``````{=IFERROR(INDEX(A\$2:A\$6,MATCH(1,(F2=B\$2:B\$6)*(G2=C\$2:C\$6),0)),"No Match")}
``````

Let me explain mechanism behind the Formula.

Generally, an `INDEX` & `MATCH` formula configured with `MATCH` set to look through a one column range and provide a match based on given criteria, without concatenate values in a helper column, or in the Formula itself.

But the shown Formula works around this limitation by using boolean logic to create an Array of `1` & `0` to represent rows matching to `2 criteria`, then using `MATCH` to match the first `1` found.

The temporary Array of `1s` & `0s` is generated with this combination.

`(F2=B\$2:B\$6)*(G2=C\$2:C\$6)`

Returns,,

```{FALSE;FALSE;TRUE;FALSE;TRUE}* {FALSE;FALSE;TRUE;FALSE;TRUE}```

Finally makes,,

`MATCH(1,{0;0;1;0;1}`

Adjust cell references in the Formula as needed.