# Solved Vlookup nested in an If Statement - having issues

Microsoft Office excel 2007 - upgrade
May 22, 2012 at 08:42:46
Specs: Windows 7
 I am trying to provide a rebate amount where the Division and Short Item number are equal it would enter the rebate amount on that line. Below is an example of the data that I am trying to perform the vlookup in; this area is called Vendor_Setup. There are many different short items which will each have a different rebate amount per area. Area Item No Rebate AmountDEN 4566 \$110.00SPG 4566 \$110.00ONT 4566 \$80.00TUC 4566 \$40.00I have tried the following function: =if(and(Vlookup(F2,Vendor_setup,3,false), Vlookup(C2,Vendor_setup,3,false), ………Where I run into problems is that this statement shows no match in the If Statement function arguments box and I know there is a match and then I am not sure what value to put for Value_if_tru section because it would have to result in the rebate amount in the row where the area and item number match. Any help would be great, thank you,

See More: Vlookup nested in an If Statement - having issues

May 23, 2012 at 08:29:50
 Hopefully this helps describe the issue.What would have helped is if you had read the How To I posted.A quick solution is to combine your data.Your Sheet 1 (I think) looks like:``` A B C D 1) Home Number Area Item Number Rebate Amount 2) 13240025 SLC 11757 3) 16910003 VIR 81140 4) 10300003 PHX 11614 ``` Your Sheet 2 looks like:``` A B C 1) Area Item No Rebate Amount 2) DEN 4566 \$110.00 3) SPG 4566 \$110.00 4) ONT 4566 \$80.00 5) TUC 4566 \$40.00 ```On Sheet 1 insert a new column between C & Dand combine your Area & Item Number with the formula: =B2&C2On Sheet 2 insert a new column between B & Cand combine your Area & Item Number with theformula: = A2&B2 Your sheet 1 should now look like:``` A B C D E 1)Home Number Area Item Number Rebate Amount 2)13240025 SLC 11757 SLC11757 3)16910003 VIR 81140 VIR81140 4)10300003 PHX 11614 PHX11614 ```Your sheet 2 like:``` A B C D 1) Area Item No Rebate Amount 2) DEN 4566 DEN4566 \$110.00 3) SPG 4566 SPG4566 \$110.00 4) ONT 4566 ONT4566 \$80.00 5) TUC 4566 TUC4566 \$40.00 ```Now it is a simple VLOOKUP() function.In Sheet 1, Cell E2, enter the formula:=VLOOKUP(D2,Sheet2!\$C\$2:\$D\$5,2,FALSE)Drag down.You can also Hide the two helper columns.See how that works.MIKEhttp://www.skeptic.com/

#1
May 22, 2012 at 12:13:42
 Although I'm not quite sure what you are trying to do, you might be getting strange results because of the way Excel handles VLOOKUPs inside an AND function.Keep in mind that the order in which the VLOOKUPs are evaluated will impact the results you get.1 - As long as all VLOOKUPs inside the AND return a number other than 0, the AND will return TRUE. 2 - If any of the VLOOKUPs return a 0, then the AND will return FALSE.3 - If any of the VLOOKUPs return #N/A, then the entire function will return #N/A.4 - In any of the VLOOKUPs return something other than a number, the AND will return a #VALUE, so the entire function will return #VALUE.Again, which ever of those things happen first will decide what the AND (or perhaps the entire function) returns.Let's assume the two VLOOKUPs return the following values...AND(3, 5) returns TRUEAND(-1, -3) returns TRUEAND(3, 0) returns FALSEAND("Fred", 1) returns #VALUEAND(1, #N/A) returns #N/AClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
May 22, 2012 at 12:20:02
 where the Division and Short Item number are equalHave no idea what you mean.So I'm not at all sure where you problem is, but why are you using =VLOOKUP() with =AND()?If all your trying to do is to check and see if a value is present, then I would advise using a =COUNTIF(), something like:=if(and(COUNTIF(Vendor_setup,F2), COUNTIF(Vendor_setup,C2)You posted some data, but with no reference to what it all means.Please read this HOW TO and repost:http://www.computing.net/howtos/sho...Also, please post your entire formula.MIKEhttp://www.skeptic.com/

Report •

#3
May 23, 2012 at 07:33:32
 Thank you for responding; I am working with two separate spreadsheets; one that has an item number and area of the country with an associated rebate amount. The other spreadsheet is the item numbers that were purchased for a certain home in an area and I need to tie the rebate amount to each line. So I am trying to create a statement that would say if the Area and Item are equal in the first spreadsheet to the area and the item on a certain row in the second spreadsheet then populate the field in the first spreadsheet with the rebate amount that should be received. The information in the first post I sent was the second spreadsheet that shows the rebates per area and item number. Below is what the first spreadsheet looks like and shows the column where I am trying to populate the Rebate Amount from the other spreadsheet based on the Area and Item Number. Home Number Area Item Number Rebate Amount13240025 SLC 11757 16910003 VIR 81140 10300003 PHX 11614 Hopefully this helps describe the issue. Thank you for your help,

Report •

Related Solutions

#4
May 23, 2012 at 08:29:50
 Hopefully this helps describe the issue.What would have helped is if you had read the How To I posted.A quick solution is to combine your data.Your Sheet 1 (I think) looks like:``` A B C D 1) Home Number Area Item Number Rebate Amount 2) 13240025 SLC 11757 3) 16910003 VIR 81140 4) 10300003 PHX 11614 ``` Your Sheet 2 looks like:``` A B C 1) Area Item No Rebate Amount 2) DEN 4566 \$110.00 3) SPG 4566 \$110.00 4) ONT 4566 \$80.00 5) TUC 4566 \$40.00 ```On Sheet 1 insert a new column between C & Dand combine your Area & Item Number with the formula: =B2&C2On Sheet 2 insert a new column between B & Cand combine your Area & Item Number with theformula: = A2&B2 Your sheet 1 should now look like:``` A B C D E 1)Home Number Area Item Number Rebate Amount 2)13240025 SLC 11757 SLC11757 3)16910003 VIR 81140 VIR81140 4)10300003 PHX 11614 PHX11614 ```Your sheet 2 like:``` A B C D 1) Area Item No Rebate Amount 2) DEN 4566 DEN4566 \$110.00 3) SPG 4566 SPG4566 \$110.00 4) ONT 4566 ONT4566 \$80.00 5) TUC 4566 TUC4566 \$40.00 ```Now it is a simple VLOOKUP() function.In Sheet 1, Cell E2, enter the formula:=VLOOKUP(D2,Sheet2!\$C\$2:\$D\$5,2,FALSE)Drag down.You can also Hide the two helper columns.See how that works.MIKEhttp://www.skeptic.com/