Lookup Formula
 

Hi,

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

Help needed guys.

New Excel User


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

--
abcdexcel
------------------------------------------------------------------------
abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021
View this thread: http://www.excelforum.com/showthread...hreadid=497118

Lookup Formula
 

Hi

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

Thanks
New Excel User


--
abcdexcel
------------------------------------------------------------------------
abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021
View this thread: http://www.excelforum.com/showthread...hreadid=497118

Lookup Formula
 

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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497118

Lookup Formula
 

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


--
abcdexcel
------------------------------------------------------------------------
abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021
View this thread: http://www.excelforum.com/showthread...hreadid=497118

Lookup Formula
 

Questions:

1 - It looks like each column has its own time frame, meaning that the
start and end date of one column don't overlap the start and end date
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 of
12.50%, 15.00% or 10.00% is possible, what percentage should be
returned?

need more details.

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497118

Lookup Formula
 

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


--
abcdexcel
------------------------------------------------------------------------
abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021
View this thread: http://www.excelforum.com/showthread...hreadid=497118

Lookup Formula
 

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 and
Codes in A5 and A6.
you can then put your formula to find the tax amount in G5 and G6, sum
of tax amount in G7.

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

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=497118

Lookup Formula
 

Thanks a lot.


--
abcdexcel
------------------------------------------------------------------------
abcdexcel's Profile: http://www.excelforum.com/member.php...o&userid=30021
View this thread: http://www.excelforum.com/showthread...hreadid=497118