Lookup formula help

[Rafeek]

Alraedy posted in microsoft.public.excel.functions newsgroup. Server is
returing duplicate post error so I am posting my corrected version of the
question here.

Sorry, I messed up and here is what I intended (hope it will come all right
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

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.

 

[Rafeek]

Thank you for your time, Ron.

I have 2 sheets named 'Sep 11 ' and 'PaySlips' in my workbook. 'Sep 11'
contains payroll data and Payslips contains paystubs of individual
employees. I entered the following array formula as suggested by you in
Payslips worksheet and it works fine.( There are additional Rows and Columns
in the 'Sep 11' sheet, so have adjusted the cell references accordingly).

{=IFERROR(INDEX('Sep 11'!$U$6:$W$6,1,MATCH(TRUE,ISNUMBER('Sep 11'!U8:'Sep
11'!W8),0)),"")}

But I need to use a Lookup formula in the Payslips worksheet so that after
setting up the Paystub for the 1st employee, I can just copy it down by
sequentially increasing Payroll ID by 1 to create paystubs for the other
employees. So tried entering the following formula but it returns an Error
in formula msg.

{=IFERROR(INDEX('Sep
11'!$U$6:$W$6,1,MATCH(TRUE,ISNUMBER((VLOOKUP(D7,PalmsPayroll,21,FALSE))VLOOKUP(D7,PalmsPayroll,23,FALSE))),0)),"")}

where Cell D7 points to the 1st Employee's Payroll ID, 'PalmsPayroll' is the
Range Name for the Payroll Data, 21 is the Column No. for Column 'U' and 23
is the Column No. for Column 'W'

Am I missing something in this formula?

TIA.

Rafeek.