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.

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)

--
Biff
Microsoft Excel MVP


"Stephanie" wrote in message
...
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.

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
on two lines and I only need one of the lines. There is another field in the
A column that defines which one I need. Is there a way to add that?



"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Stephanie" wrote in message
...
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.

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)

--
Biff
Microsoft Excel MVP


"Stephanie" wrote in message
...
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
on two lines and I only need one of the lines. There is another field in
the
A column that defines which one I need. Is there a way to add that?



"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Stephanie" wrote in message
...
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.