Sep 22 2020 09:07 AM
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!
Sep 22 2020 11:54 AM
Sep 22 2020 12:08 PM
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)
Sep 23 2020 05:46 AM
It can be more or less. @Bennadeau
Sep 23 2020 05:47 AM
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
Sep 24 2020 06:01 AM
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
Sep 24 2020 08:41 AM
That works! Thank you soooo much!!!@Bennadeau