VLOOKUP

Question

I am having the most trouble with this. How would you do this. 

In cell B17, calculate the amount of federal tax by multiplying the Total Pay Value in cell B14 by the appropriate federal tax rate for the employee with the marital status in cell B7 and withholding allowances in cell B9 (hint:Use the VLOOKUP function with the exact match lookup for the lookup table in the range L6:W8. For the col_index_num argument, use the value of B9 plus 2.)

I have written mine like this.  

=VLOOKUP(L6:W8,B7:B9,3,true)

Then I tried

=VOOKUP(L6:W8,B7:B8,B9+2,True)

both are wrong, can you please offer some sort of guidance.

Answer 1

The general form of VLOOKUP is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value can only be a single reference or value, not a range like you have done.

It's hard to tell from the question exactly what is required without seeing some of the data, but the VLOOKUP part of the formula is likely to be something like:

=VLOOKUP(B7,L6:W8,B9+2,FALSE) and the overall formula in B17 like:

=B14*VLOOKUP(B7,L6:W8,B9+2,FALSE)

Perhaps try that, but if it doesn't work, can you reply with the values in B7 and B9, and a snapshot of the range L6:W8. From there we can probably work out the correct answer.

Answer 2

Please update us on your problem. Are you still facing the issue? Do you still require help?

Answer 3

Did you get an opportunity to share a sample file without confidential data?

Answer 4

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link? It will help me to give prompt and high quality solution.

Answer 5

Hi,

How to get all occurrences of lookup value (duplicates)

As mentioned above, the Excel VLOOKUP function cannot get duplicate instances of the lookup value. To do this, you would need a far more complex array formula consisting of several Excel functions such as INDEX, SMALL and ROW.

For instance, the below formula finds all instances of the value in cell F2 in the lookup range B2:B16, and returns values from column C in the same rows:

{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}

Copy the below formula to several adjacent cells, e.g. cells F4:F8 as demonstrated in the screenshot below. The number of cells where you copy the formula should be equal to or larger than the maximum number of possible duplicate entries. Also, remember to press Ctrl + Shift + Enter to enter an array formula correctly.

Let me know if that helps.

Kind Regards,

Engr. Jamshed Muhammad