List Lookup or Directory (not sure what it should be called)

[Shelley0605]

I do environmental work that includes working with wetlands. The National Wetlands Inventory contains codes for every type of wetland/stream combination. There are thousands of possibilities. For instance, PFO1A = Palustrine (the P); Forested (the FO); Broad-Leaved Deciduous (the 1); Temporarily Flooded (the A). I can build a list for all of the possibilities, but I am looking for a lookup formula so if I build the list on one sheet and type the code "PFO1A" into cell A1 on another sheet, it will give me the above description in the cell A2. Also, it would be even better if I could create a lookup list with just the individual characters and it would export the whole string instead of building a table with all possibilities. 

I hope this makes sense. 

Thanks in advance!

[#2]

Hi @Shelley0605,

is your code always 5 character or can it be more or less? (PFO1A)

[#3]

@Shelley0605 

This won't work if you input anything other than 5 character. If 5 is what you need, this should meet your needs.

=VLOOKUP(MID(D2,1,1),A:B,2,0) & " | " & VLOOKUP(MID(D2,2,1),A:B,2,0) & " | " & VLOOKUP(MID(D2,3,1),A:B,2,0) & " | " & VLOOKUP(MID(D2,4,1),A:B,2,0) & " | " & VLOOKUP(MID(D2,5,1),A:B,2,0)

[#4]

It can be more or less. @Bennadeau 

[#5]

I do not mind typing all of the different outputs. I can just add them as we use them. Would this work for that scenario as well? @Bennadeau 

[#6]

@Shelley0605 

Apologies for the delay, I was trying to find a more suitable solution for your problem. Unfortunately, whatever approach I took brought a new issue.

So, if you want to write all the possibilities (might take a while) then the following formula should work for you.

=VLOOKUP(D1,A:B,2,0)

Where 

D1 = the value you are looking up

A:B = the table with the codes in column A and the description in column B

2 = the column index # from where the value is returned

0 = Find the exact match

[#7]

That works! Thank you soooo much!!!@Bennadeau