Formula Question

[Stephanie]

I need to create a formula to do the following:

LOC STAT FACILITY PROF
ADJ VOID 0 0
COB COB 0 0
COB LNLVL 0 0
COB MED 0 6
COB PEND 14 31
COB SPLIT 0 0

I need Excel to find the matching text in column B (ex. LNLVL) and then
return the value found in Facility and Prof columns.

Can anyone help me? I have tried using help and can't get it to work.

 

[T. Valko]

One way, assuming LNLVL is a unique entry:

Enter this formula in one cell and copy across to a second cell:

=SUMIF($B2:$B7,"LNLVL",C2:C7)

 

[Stephanie]

I answered that this did not work but it did. I just had to tweak it a
little. One other question though. One of the text values MEDB is located

 

[T. Valko]

If there are duplicate entries in the STAT column then it becomes somewhat
complicated.

Try this array formula** entered in one cell then copied across to a second
cell:

=INDEX(C2:C7,SMALL(IF($B2:$B7="med",ROW(B2:B7)-MIN(ROW(B2:B7))+1),n))

Where n = instance number that you want to look for. n can be a cell
reference like A1.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)