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.

 

[Ron Rosenfeld]

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.

D2:

This formula must be **array-entered**:

=IFERROR(INDEX($B$1:$C$1,1,MATCH(TRUE,ISNUMBER(B2:C2),0)),"")

or, if your version of Excel is prior to 2007:

=IF(COUNT(B2:C2)=0,"",INDEX($B$1:$C$1,1,MATCH(TRUE,ISNUMBER(B2:C2),0)))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

E2: =IF(COUNT(B2:C2)=0,"",SUM(B2:C2))

Select D2:E2 and fill down as far as required.

 

[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.

 

[Ron Rosenfeld]

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.

It frequently causes problems when one tries to simplify a problem in the hopes that the solution to the simple problem might be applicable to the "real" problem.

For the data format you originally supplied, simply filling down the formula would have returned the sequential payslip amounts.

However, in this case,
your "real" data layout is not the same as what you presented
your results are not in columns adjacent to the data, but rather on a different worksheet altogether
you want to key of a Payroll ID number which you did not present at all
your columns of relevant data (the 1% and 7% columns) are not adjacent and we have no idea what is in between
and it also seems that the order of the employees for your Payslips sheet is not the same as the order in the original table

All of these factors needed to have been taken into account in devising a solution. The original solution will not work, as you have found out. Neither of the original formulas was designed to take all of those undisclosed factors into account.

Hopefully this solution will have some relevance to your real data and what you want to do

To return the appropriate %, try:

=IF(VLOOKUP(D7,PalmsPayroll,3,FALSE),$C$1,IF(VLOOKUP(D7,PalmsPayroll,5),$E$1,""))

Where, as you wrote D7 is a Payroll ID.

Also, in PalmsPayroll, the first column must be the payroll ID;

In the above formula, replace "3" with the appropriate column number that corresponds to your "1%" label (and replace "5" similarly for the 7% label). The appropriate Column number depends on which column Payroll ID is located in. So if Payroll ID were in Column A, the 1% label in column U, and the 7% label in column W, you would indeed use 21 and 23 in place of 3 and 5. However, if Payroll ID were in Column K, then you would be substituting 11 and 13.

Finally, I am assuming that you will replace $C$1 with $U$6, and $E$1 with $W$6 (or whatever row in columns U and W have the labels).

 

[Ron Rosenfeld]

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.

I forgot to post the formula to return the date (5 ,42 and blank in your original):

=IF(VLOOKUP(D7,PalmsPayroll,3,FALSE),VLOOKUP(D7,PalmsPayroll,3,FALSE),
IF(VLOOKUP(D7,PalmsPayroll,5,FALSE),VLOOKUP(D7,PalmsPayroll,5,FALSE),""))

Again, replace the "3"'s and the "5"'s as I outlined in the previous post.