Lookup Formula

[Rafeek]

In a table, two of the columns contain the following:

Col A Col. B Col. C
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
Row 3 Peter Blank 42
Row 4 David Blank Blank

and so on.

For each Row, both Col. B and C can be blank but both of them can not be non
blank. In Columns D & E, I want a look up formula which returns the
following results:

Col A Col. B Col. C
Col. D Col. E
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
1% 5
Row 3 Peter Blank 42
7% 42
Row 4 David Blank Blank
Blank Blank


I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and other pay nothing.

Any help will be highly appreciated.

TIA.

Rafeek.

 

[FatBytestard]

I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and other pay nothing.

Any help will be highly appreciated.

TIA.

Rafeek.

Try reposting using a proportional font.

 

[Walter Briscoe]

In message <[email protected]> of Mon, 26 Sep
2011 02:32:28 in microsoft.public.excel.worksheet.functions,

Try reposting using a proportional font.

That may be the issue. It may also be the OP has copied data from Excel
and pasted it into Microsoft Outlook Express 6.00.2900.3138.
Doing that causes column values to be padded with tab characters which
may be converted to sequences of one or more spaces.
I agree that a proportional font is inappropriate for table
illustrations.

I think the OP posted

A B C
1 Employee Name 1% 7%
2 Williams 5
3 Peter 42
4 David

and

A B C D E
1 Employee Name 1% 7%
2 Williams 5 1% 5
3 Peter 42 7% 42
4 David

If that is so,
1) Make the format of Column D, percentage with 0 decimal places (or
make all data text).
2) Set D2 to =IF(B2<>"",$B$1,IF(C2<>"",$C$1,"")) and copy down.
3) set E2 to =IF(D3=$B$1,B3,IF(C3<>"",C3,"")) and copy down.

N.B. I don't check (Bn<>"") <> (Cn<>""). i.e. I don't check that both 1%
and 7% are not checked.


I think I would prefer to see the data in two tables. e.g.

A B C D
1 Emp Name Pay Rate Sum
2 Williams 5 1 5
3 Peter 42 2 42
4 David 0

A B
1 Code Rate
2 0
3 1 1%
4 2 7%

I confess I do not have a full understanding of the OP's data.

 

[Rafeek]

Sorry, I messed up. What I intended to post was this: (hope it will come all
risght this time)

In a table, three columns contain the following:

Col A Col. B Col. C
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
Row 3 Peter Blank 42
Row 4 David Blank Blank
.................
.................

In each Row, both Col. B and C can be blank but both of them can not be non
blank. In Columns D & E, I want a look up formula which returns the
following results:

Col. A Col. B Col. C
Col. D Col. E
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
1% 5
Row 3 Peter Blank 42
7% 42
Row 4 David Blank Blank
Blank Blank
................
................

I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and others pay nothing. If there are any deductions, it will be either 1%
or 7%. So in 2 adjoing cells in the Pay Slip, I want to show the % and the
amount deducted. If nothing was deducted, I want to leave these two cells
blank.

Any help will be highly appreciated.

TIA.

Rafeek.

 

[Bartt]

Sorry, I messed up. What I intended to post was this: (hope it will come all
risght this time)

In a table, three columns contain the following:

                Col A                       Col. B        Col. C
Row 1      Employee Name             1%           7%
Row 2      Williams                        5              Blank
Row 3      Peter                           Blank           42
Row 4      David                          Blank          Blank
................
................

In each Row, both Col. B and C can be blank but both of them can not be non
blank. In Columns D & E, I want a look up formula which returns the
following results:

               Col. A                   Col. B            Col. C
Col. D    Col. E
Row 1    Employee Name            1%                7%
Row 2    Williams                        5                    Blank
1%        5
Row 3    Peter                            Blank            42
7%        42
Row 4    David                           Blank               Blank
Blank    Blank
...............
...............

I need this for a Paystub Report in my excel 2007 payroll workbook. Undera
particular head some employees are charged 1% of their basic pay, some pay
7% and others pay nothing. If there are any deductions, it will be either1%
or 7%. So in 2 adjoing cells in the Pay Slip, I want to show the % and the
amount deducted. If nothing was deducted, I want to leave these two cells
blank.

Any help will be highly appreciated.

TIA.

Rafeek.















- Show quoted text -

I'm not really sure I understand what you're trying to do.

If I understand correctly, each employee can work for a head & each
head has a specified tax rate, which is either 1%, 7%, or blank which
would probably be better handled as 0%, but you may be handling that
elsewhere with a formula.

It's technically possible to do what you've asked for with an OFFSET()
function that has a nested MATCH().

However, if each head has only one tax rate, it would seem to make
more sense to break the heads into a separate list, then lookup that
head's tax rate.

Or have I missed it entirely?