Too Complicated for a Title

[Brandon Carraway]

I have a rather (complicated?) request for formula assistance.

I have a spreadsheet that I am making for my small business to track sales, inventory, customers, and pricing and margin.

I'm at the point of getting excel to check for customers that should be charged Sales Tax.
I've done pretty well so far, but have hit a brick wall in this particular formula (my request is too long? for google). XD

Basically, I need Excel to =VLOOKUP? 'Purchases'!C2 against 'Customer List'!A:A; once the customer is found, look to see if the corresponding G:G says "TX", if it does, then 'Purchases'!H2="8.25%", if not, then "0.00%".

-Is this too complicated?
I've tried VLOOKUP for this, but not sure how to do it; Do I need to use an "=IF" function in conjunction with =VLOOKUP?
-Is =VLOOKUP even the appropriate function to use?

Please see the attached Spreadsheet for reference...

 

[NARAYANK991]

Hi ,

Will this work ?

=IF(VLOOKUP(C2,'Customer List'!A:J,7,0) = "TX", 8.25%, 0%)

Narayan

 

[Hui]

In H2:
=IF(INDEX('Customer List'!G2:G1000,MATCH(C2,'Customer List'!A2:A1000,0))="TX",8.25%,0%)
Copy down

 

[Brandon Carraway]

Hi ,

Will this work ?

=IF(VLOOKUP(C2,'Customer List'!A:J,7,0) = "TX", 8.25%, 0%)

Narayan

This works! I just need to put an =IF(C2="", "", formula, because I quick fill the formula to the bottom (set it and forget it).

Great formula though! I guess I was just over-complicating it in my head. (I tend to do that). XD

 

[Brandon Carraway]

In H2:
=IF(INDEX('Customer List'!G2:G1000,MATCH(C2,'Customer List'!A2:A1000,0))="TX",8.25%,0%)
Copy down

Your formula did not work as expected - However, it was because you were too specific with the column references (Or I just mis-typed the formula numerous times[highly possible]). Please see amended formula (I played w/ your formula a bit) below:

=IF(INDEX('Customer List'!G:G, MATCH(C2, 'Customer List'!A:A, 0)) = "TX", 8.25%, 0%)

Thank you for your response! It definitely helped me out a lot!

 

[Brandon Carraway]

EDIT:
In case anyone is wondering, this is the final formula I went with:

=IF(D3="","",IF(VLOOKUP(C3,Customers!A:G,7,0)="TX",8.25%,0%))

BREAK DOWN:
IF D3 is blank, then return a blank value (instead of #VALUE!) useful if you fill your formula all the way to the bottom, and don't want to see the error.

Then, VLOOKUP(C3 basically takes the value in C3, and looks for the exact match (defined by the "0") in the tab Customers within the 'table' of columns A-G, and checks the 7th column from A for "TX". If it finds "TX", then it gives the value "8.25%", and if it does not find "TX", then it goes with "0%".

If you don't want an exact match, but rather a close value, then remove the "0" in the VLOOKUP formula.