If N/A then look up first 3 digits of different cell and value from a table.

[anyoder]

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Zip [/TD]
[TD]54032[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]License[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 546"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
I want a formula that only populates D2 if B2 is #N/A. Formula will look at the first three digits B1 and and look-up the corresponding state on a different table and return the state abbreviation into cell D2.

Suggestions are greatly appreciated.

 

[Joe4]

Something structured like:

=IF(ISNA(B2),VLOOKUP(LEFT(B1,3),...),"")