combine a vlookup and an if/then

[htchandler]

so I have to use a vlookup to obtain the information in the 'If' column. The problem is I need the information displayed in a different format (the then column):

if: then:
01 2LT
02 1LT
03 CPT
04 MAJ
05 LTC

Anyone have any idea how to structure the formula(s)?

 

[Robert Mika]

Does normal VLOOKUP not work for you?
What kind of a format do you need to obtain?

Can you elaborate youer problem a little bit more?

 

[htchandler]

This is all looking at data on Soldier's, the problem is the return from the vlookup is the grade of the Soldier, not the rank, the rank is not on the spreadsheet that is being looked at. I know that a O5 is a LTC, an O4 is a MAJ, etc. I just need the returned data from the vlookup (e.g. O5) to somehow be converted to LTC or MAJ, or whatever the equivelant is. I know all the equivelants, I just don't know how to program excel to do the conversion for me.

 

[excelsishya]

Hi,

Assuming there are only 5 Ranks, vlookup is giving number which is custom formated to give "01" and IF data starts at A1

Use at any blank column
=IF(A1=1,"2LT",IF(A1=2,"1LT",IF(A1=3,"CPT",IF(A1=4,"MAJ",IF(A1=5,"LTC","GENERAL")))))

Replace A1 with your Vlookup formula if you dont want use helper column.

 

[T. Valko]

This is all looking at data on Soldier's, the problem is the return from the vlookup is the grade of the Soldier, not the rank, the rank is not on the spreadsheet that is being looked at. I know that a O5 is a LTC, an O4 is a MAJ, etc. I just need the returned data from the vlookup (e.g. O5) to somehow be converted to LTC or MAJ, or whatever the equivelant is. I know all the equivelants, I just don't know how to program excel to do the conversion for me.

So why not just add the ranks to the lookup table?

If there are more than a "few" variables then I like to use tables which would mean a nested lookup function.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Grade</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Rank</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">01</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2LT</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">02</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1LT</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">03</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">CPT</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">04</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">MAJ</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">05</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">LTC</TD></TR></TBODY></TABLE>


Something like this...

=VLOOKUP(VLOOKUP(…),A1:B6,2,0)

Where the inner VLOOKUP is your current lookup formula that's returning the grade.

 

[htchandler]

Doing the dual vlookup's worked perfect, I didn't even know that was possible. Thank you very much.

Henry

 

[T. Valko]

Doing the dual vlookup's worked perfect, I didn't even know that was possible. Thank you very much.

Henry

You're welcome. Thanks for the feedback!