The long example formula is first checking if the D3 can be found from the Table1. If not, the it'll return the "Missing GL Number". If a match is found then it checks, if the second cell next to the D3 match is empty (or zero) and returns "DNP" if so. If there's something in the next cell then it returns that value and checks how many characters there are in it. If it's 7 characters long, the it'll add a space. If it's not 7 characters long it'll return "-" and the matching value for C3 from the 2nd column in Table2.

Since none of the VLOOKUPs are using the FALSE parameter the searched ranges must be arranged in ascending order and they will return the closest smaller match if no exact matches are found (if you're looking for the match to 10 and there list goes "3, 5, 9, 11" the VLOOKUPs return the match for number 9 since 10 is missing - and if 10 is after 11 the lookups don't look that far).

The lookup formula to return the matching customer address would be something like

=VLOOKUP(B2,Table1,4,false)

This returns the exact match for the B2 from the 4th column in the range Table1. Correct the B2 and change the 4 into the right number since I might have misunderstood your description.

Hope this helps.

## Like this thread? Share it with others