Formula and Vlookup Help

[dracron]

I have two work sheets where I am using a vlookup to return data (a status to be more specific)

The result could be one of 16 different statuses that we use.

What I would like to do is, based on the status that is returned, it converts to an abbreviation (See below for example)
Right now I am using a run on formula like this:

=IF(VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false)="Fully Licensed","L",IF(VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false)="Action Required","A",IF(VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false)="Pending","p", etc. etc

As you can imagine it is very long.

Is there a way to condense this? I do not want to add an additional column to do another vlookup where it looks at the return result and then vlookup off the status/abbreviation list.
Id like to be contained in one formula.

Status and abbreviation examples:
[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]
</TBODY>[/TABLE]


Thank you

 

[bbott]

Maybe something like this:

=LOOKUP(Sheet1!A1,{"Action Required","PE completed","Pending","Schedule PE","Test Scheduled"},{"AR","PC","PN","SE","T"})

Your lookup items need to be in alphabetical order for this to work correctly.

 

[dracron]

Maybe something like this:

=LOOKUP(Sheet1!A1,{"Action Required","PE completed","Pending","Schedule PE","Test Scheduled"},{"AR","PC","PN","SE","T"})

Your lookup items need to be in alphabetical order for this to work correctly.

How do you combine this with the first vlookup?

I am doing a vlookup first that finds what the current status is, then based on that result I want it to convert to the abbreviation

 

[bbott]

Try:

=LOOKUP(VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false),{"Action Required","PE completed","Pending","Schedule PE","Test Scheduled"},{"AR","PC","PN","SE","T"})

 

[Marcelo Branco]

This formula is not correct. It doesn't work.
VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false)

The second argument, table array, is S1:S5 - a range with just one column.
The third argument, col_index_num, is equal 5 . i.e. the formula should return the 5th column - this is not coherent

M.

 

[bbott]

This formula is not correct. It doesn't work.
VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false)

The second argument, table array, is S1:S5 - a range with just one column.
The third argument, col_index_num, is equal 5 . i.e. the formula should return the 5th column - this is not coherent

M.

Good catch Marcelo...I didn't look that closely at the original formula.

 

[dracron]

.

 

[dracron]

Try:

=LOOKUP(VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false),{"Action Required","PE completed","Pending","Schedule PE","Test Scheduled"},{"AR","PC","PN","SE","T"})

Thank you I think I can get this to work for me.
Appreciate it

 

[dracron]

This formula is not correct. It doesn't work.
VLOOKUP(sheet1!A1,Sheet2!S1:S5,5,false)

The second argument, table array, is S1:S5 - a range with just one column.
The third argument, col_index_num, is equal 5 . i.e. the formula should return the 5th column - this is not coherent

M.

[h=2]

Re: Formula and Vlookup Help[/h]

Its just an example of what I was doing, it was just a mis-type. If you also read, I do not want to use that type of formula so nitpicking over an irrelevant detail waists time
Please refer to the question I am asking.

"I am doing a vlookup first that finds what the current status is, then based on that result I want it to convert to the abbreviation".

Fictional cells for example can be used to demonstrate​

 

[Fanwood]

This will do it for you

Do your first vlookup (in bold) which will return the status and then use the table you have the abbreviations in for the 2nd vlookup which will convert to your abbreviation

=VLOOKUP(VLOOKUP(A5,sheet2!A:D,4,FALSE),AG:AH,2,FALSE)