Microsoft Office excel 2007 - upgrade

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,

✔ Best Answer

Hopefully this helps describe the issue.What would have helped is if you had read the

How ToI 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 11614Your 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.00On Sheet 1 insert a new column between C & D

and combine your Area & Item Number with the

formula: =B2&C2On Sheet 2 insert a new column between B & C

and combine your Area & Item Number with the

formula: = A2&B2Your 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 PHX11614Your 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.00Now 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

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.

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

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 11614Hopefully this helps describe the issue.

Thank you for your help,

Hopefully this helps describe the issue.What would have helped is if you had read the

How ToI 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 11614Your 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.00On Sheet 1 insert a new column between C & D

and combine your Area & Item Number with the

formula: =B2&C2On Sheet 2 insert a new column between B & C

and combine your Area & Item Number with the

formula: = A2&B2Your 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 PHX11614Your 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.00Now 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

Thak you very much. I am sure that will work. Thank you for the great suggestion.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History