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

[anyoder]

	A	B	C	D
1	Zip	54032
2	License	#N/A
3
4

<tbody>
</tbody>

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

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),...),"")