Lookup Formula

[abcdexcel]

Hi,

I want to use vlookup formula using two vlookup value. Excel workshee
is attached.

Help needed guys.

New Excel Use

+-------------------------------------------------------------------
|Filename: query.zip
|Download: http://www.excelforum.com/attachment.php?postid=4166
+-------------------------------------------------------------------

 

[abcdexcel]

Hi

Depending upon the data entered in one cell, the other cell needs to be
protected or unprotected.

Thanks
New Excel User

 

[pinmaster]

Looks to me like your tax rate is based on more than just B1 and B2
since there are 3 possible rates for each place and date.

Regards
JG

 

[abcdexcel]

Yes Sir. I want a function or a combination of two or more formulae
wherein first it finds out the Area (place) and then ascertain the date
& returns with the Tax Rate.

Thanks & Regards,
New Excel User

 

[pinmaster]

Questions:

1 - It looks like each column has its own time frame, meaning that th
start and end date of one column don't overlap the start and end dat
of the previous or following columns, is that the case?

if that is the case then a lookup on just the date alone would do
using an HLOOKUP formula.

=HLOOKUP(B1,D14:I18,???,1)


2 - How is the tax rate determined? In your example a tax rate o
12.50%, 15.00% or 10.00% is possible, what percentage should b
returned?

need more details.

Regards
J

 

[abcdexcel]

Sir,

The rates do not overlap.

For each product, there is a different rate for various period.
Secondly, the rate also depend on the place selected in cell B2.

Thus, the formula should first identify the place, then identify the
product code, then identify the date and return the sales tax rate from
the table.

Thanks & Regards,
New Excel User

 

[pinmaster]

Then I suggest you take another approach, try this:

in F5
=IF(A5="","",SUMPRODUCT(($D$13:$I$13=$B$2)*($D$14:$I$14<=$B$1)*($D$15:$I$15>=$B$1)*($A$16:$A$18=$A5),$D$16:$I$18))
copy down to F6
this will find the tax rates based on the Place in B2,Date in B1 an
Codes in A5 and A6.
you can then put your formula to find the tax amount in G5 and G6, su
of tax amount in G7.

Note. you will have to ajust the ranges to your needs.

HTH
J

 

[abcdexcel]

Thanks a lot.