Vlookup/Formula Help

[Fanwood]

Hi all,

I am looking for advice/solution to a vlookup question.
I have a Key that lists 16 words and their abbreviations.


What I would like to do is a vlookup from one cell to another and then result converts to the abbreviation that is listed in the key without using some massive long formula like below, but I am not sure how to proceed. Right now I have a formula where I typed out every single word from the key and converts using an if formula. There has to be a better way right??

Here is a small example of what I am currently using

=IF(VLOOKUP(A1,S1:S5,5,false)="Fully Licensed","L",IF(VLOOKUP(A1,S1:S5,5,false)="Action Required","A",IF(VLOOKUP(A1,S1:S5,5,false)="Pending","p", etc. etc
it goes on for 16 different outcomes!! Yikes

Thanks in advance

 

[Special-K99]

What are your 16 different values?

Create a table of lookup descriptions and return values
The just perform one VLOOKUP
e.g.

=VLOOKUP(A1,Sheet2!A1:B16,2,0)

 

[Fanwood]

[TABLE="width: 364"]
<TBODY>[TR]
[TD]Action Required</SPAN>[/TD]
[TD]AR</SPAN>[/TD]
[/TR]
[TR]
[TD]Pending</SPAN>[/TD]
[TD]PN</SPAN>[/TD]
[/TR]
[TR]
[TD]Scheduled PE</SPAN>[/TD]
[TD]SE</SPAN>[/TD]
[/TR]
[TR]
[TD]PE Completed</SPAN>[/TD]
[TD]PC</SPAN>[/TD]
[/TR]
[TR]
[TD]Test Scheduled</SPAN>[/TD]
[TD]T</SPAN>[/TD]
[/TR]
[TR]
[TD]Test Passed</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]Test Failed</SPAN>[/TD]
[TD]F</SPAN>[/TD]
[/TR]
[TR]
[TD]No Show</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[/TR]
[TR]
[TD]Re-Take Scheduled</SPAN>[/TD]
[TD]RT</SPAN>[/TD]
[/TR]
[TR]
[TD]Documents Obtained</SPAN>[/TD]
[TD]DO</SPAN>[/TD]
[/TR]
[TR]
[TD]Need to Submit App</SPAN>[/TD]
[TD]SR</SPAN>[/TD]
[/TR]
[TR]
[TD]Application Made</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[/TR]
[TR]
[TD]Fully Licensed</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[/TR]
[TR]
[TD]Did Not Complete</SPAN>[/TD]
[TD]DNC</SPAN>[/TD]
[/TR]
[TR]
[TD]Prev Lic Obtained</SPAN>[/TD]
[TD]LO</SPAN>[/TD]
[/TR]
[TR]
[TD]N/A</SPAN>[/TD]
[TD]N/A</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

 

[Fanwood]

.

 

[Fanwood]

What are your 16 different values?

Create a table of lookup descriptions and return values
The just perform one VLOOKUP
e.g.

=VLOOKUP(A1,Sheet2!A1:B16,2,0)

That doesn't work because it is looking at two different worksheets, it pulls over the word "Action required", but then I want that to convert to the abbreviation.
If I use something like =VLOOKUP(A1,Sheet2!A1:B1000,2,0) then I would have to put a new column

The vlookup based on persons ID number that pulls over the status from one sheet to the other (which could be one of the list provided) I then need that to convert to the abbreviation.
Just looking to not do it by the long if formula or by adding a new column.

 

[Fanwood]

What are your 16 different values?

Create a table of lookup descriptions and return values
The just perform one VLOOKUP
e.g.

=VLOOKUP(A1,Sheet2!A1:B16,2,0)

I figured it out:

=VLOOKUP(VLOOKUP(C5,'PCE and States'!C:V,4,FALSE),AG:AH,2,FALSE)

It was staring at me the whole time