Excel - Looping through data till it finds the right one

Question

I'm pulling data from one Excel table into another using an IF statement. I want it to check two fields and if it is a match I want it to print something and if it doesn't then I want it to continue searching. If there is no absolute match then leave the field blank.

I believe I'm running into an syntax issue but after numerous iterations I can't get it to pull everything over. Here is my current syntax.

=IF(BM5<>"External","",IF(AND(S5=VLOOKUP(A5,ExternalOnly,5,FALSE),A5=VLOOKUP(A5,ExternalOnly,1,FALSE)),S5,"")

Answer 1

Add an additional ')' at the end of the formula and see if this works.

i.e. try this

=IF(BM5<>"External","",IF(AND(S5=VLOOKUP(A5,ExternalOnly,5,FALSE),A5=VLOOKUP(A5,ExternalOnly,1,FALSE)),S5,""))

Answer 2

I use this:

INDEX($E$1:$E$7,MATCH(A7,$D$1:$D$7,0))

Here is a sample table to illustrate. the formula is in the cells of column B (B7 in this case). How it works is the match finds the corresponding entry in the target list ($D$1:$D$7) for our selected value (A7). It returns the index from that list and the INDEX() function lets us select a different column from the matched row to return.

A         B      C     D       E
------    ---   ---    ------  ---
011597  99        012062 3
012062  3         012142 8
012136  3        011597 99
012142  8         012136 3
014157  2         014157 2
011582  87         011582 87
011707  101         011707 101