I have a worksheet, similar to the one in this image;

enter image description here

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
  • 1
    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
  • 1

Your issue can be solved by using Index and Match combination.

enter image description here

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.




Finally makes,,


Adjust cell references in the Formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.