Help with VLOOKUP or a better method

[timmiller420]

I am very rusty and diving back into Excel a little. I have to update a price list and I think VLOOKUP function with get me there with multiple criteria but not sure. Can someone help steer me in the right direction?

For ex: in the file attached the two criteria points will be matching the PLU number and the location/price group .....then updating the new price where need in the main list on the left.

For example the first one: PLU 10408 at location/price group Seaford.....need to replace the current price on the left table with the the new price on the right table.

I will be glad to donate to someone via paypal for any help or time will be greatly appreciated.

Trying to avoid having to manually change 491 prices....ugh

File attached

[TMS]

Try:

Please Login or Register  to view this content.

committed with Ctrl-Shift-Enter and copied down.

[timmiller420]

Im not having any luck with that...im reattaching the file where i pasted the formula in for PLU 10408 and its not returning anything....could u give me an example please?

[hrlngrv]

Do you really mean you're using Excel 2003? If so, you don't have IFERROR and I doubt you have SUMIFS or COUNTIFS.

The much bigger problem you have is that A35 is 10408 and B35 is CROIS S/E/C. OTOH, J2 is 10408 and K2 is Sausage Egg & Cheese Croissan'wich. If you only need to match col A against col J, no big deal. However, that doesn't seem to be the case. Your columns B and K don't match.

For that matter, cols A to D span rows 2 to 18145, but cols J to M span only rows 2 to 492. I added formulas.

E2: =MATCH(A2,$J$2:$J$492,0)

filled down into E3:E18145. Only 861 of those 18144 records have col A matches in col J. What should happen with the other 17,283 records? Delete them?

If all you need to do is match col A and col C against col J and col L, then replace col B and col D values with corresponding values from col K and col M, and ignore records with no matches in cols J and L, I'd copy A1:D18145 to Q1:T18145, then

P2: =MATCH(1,(Q2=$J$2:$J$492)*(S2=$L$2:$L$492),0)

filled down into P3:P18145. These are array formulas. Type the P2 formula then hold down [Ctrl] and [Shift] keys before pressing [Enter].

R2: =IF(COUNT($P2),INDEX(K$2:K$492,$P2),"DELETE ME")

filled down into R3:R18145, then copy R2:R18145 and paste into T2:T18145. Select Q1:T18145, run Data > Filter > Filter. That should put drop-down arrows in Q1:T1. Click on the drop-down for R1, select DELETE ME. That should filter through only records in which cols Q and T show DELETE ME. Select P2:T18146, and press [Delete] to clear those cells. The filtered out records will be unaffected. Run Data > Filter > Filter again to remove the autofilter. Select P1:T18145, and sort of col Q in ascending order, and BE SURE TO CHECK THE BOX FOR HEADER ROW. That should produce records in P1:T491. Those would be the only records in common between the tables in cols A to D and J to M.