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 Amount
DEN 4566 $110.00
SPG 4566 $110.00
ONT 4566 $80.00
TUC 4566 $40.00
I 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

Report •

✔ Best Answer
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 & D
and combine your Area & Item Number with the
formula: =B2&C2

On Sheet 2 insert a new column between B & C
and combine your Area & Item Number with the
formula: = 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.

MIKE

http://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 TRUE
AND(-1, -3) returns TRUE
AND(3, 0) returns FALSE
AND("Fred", 1) returns #VALUE
AND(1, #N/A) returns #N/A

Click 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 equal
Have 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.

MIKE

http://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 Amount
13240025 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
✔ Best Answer
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 & D
and combine your Area & Item Number with the
formula: =B2&C2

On Sheet 2 insert a new column between B & C
and combine your Area & Item Number with the
formula: = 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.

MIKE

http://www.skeptic.com/


Report •

#5
May 23, 2012 at 08:56:37
Thak you very much. I am sure that will work. Thank you for the great suggestion.


Report •

Ask Question