Match Multiple (5) Criteria

[switzd0d]

Tried many tutorials and I am sooo close, but I cannot get past matching 4.

Anyway, here is what I got:


I need a formula for A1 to determine what rank is supposed to go in it to match the current rank of the individual in B1.

However, here is where it gets fun. I have data from another roster workbook that is being imported into some hidden columns.

C Column pulls every persons name from another roster and puts them all in column C.

D Column pulls every persons current rank from another spreadsheet and lists them in Column D beside their correct owner in Column C

Finally, Column E contains hard data of all the ranks written out. So "PFC" is "Private First Class".

It is these long rank descriptions (Private First Class, Lance Corporal, etc) that I want to have shown in Cell A1


So in the example below, A. Switz would be a Lance Corporal so A1 should output "Lance Corporal"

A - Current Rank (long version)	B - Player Name	C - Imported Data/ Names	D - Imported Data/ Their Current Rank	E - Rank Abbreviations	F - What A1 needs to Display (long version)
NEED THIS FORMULA	A. Switz	J. Engel	CPL	PVT	Private
		B. Angel	SGT	PFC	Private First Class
		J. Malone	PVT	LCPL	Lance Corporal
		A. Switz	LCPL	CPL	Corporal
		F. Elkins	PVT	SGT	Sergeant
				SSGT	Staff Sergeant

[Phuocam]

Try this in A1...
=INDEX($F$1:$F$10,MATCH(INDEX($D$1:$D$10,MATCH(B1,$C$1:$C$10,0)),$E$1:$E$10,0))

[switzd0d]

Nailed it! Thanks so much!

[Phuocam]

You're welcome!