Forum Discussion
Excel..nested vlookup possibly..looking for help
Hi All
Column A & B are exported form pricing software - Column A is product codes. Column B is current product prices.
Column C & D are exported from daily salesbooks every morning. C is Product Codes and D is the price they were charged.
I want to check that if a product code in column C has turned up on the daybooks that the price in column D matches the price in Column B.
| Product Code | Vat Incl Price | Product Code | Unit Price (Incl) |
| 2090018 | 2079245 | 60 | |
| 2072148 | 86 | 2079245 | 60 |
| 2072145 | 90 | 2079245 | 60 |
| 2072156 | 228 | 2079245 | 60 |
| 2071117 | 2079245 | 60 | |
| 2071165 | 2079245 | 60 | |
| 2079292 | 2079245 | 60 | |
| 2072152 | 25 | 2079245 | 60 |
| 2071183 | 500 | 2079245 | 60 |
| 2071182 | 250 | 2079245 | 60 |
| 2076169 | 90 | 2079245 | 60 |
| 2076163 | 62 | 2076165 | 310 |
| 2076159 | 74 | 2076165 | 310 |
| 2079111 | 2076165 | 310 | |
| 2071179 | 338 | 2076165 | 310 |
| 2079337 | 440 | 2076165 | 310 |
| 2075116 | 39 | 2076165 | 310 |
| 2075176 | 99 | 2071125 | 48 |
| 2079245 | 60 | 2071125 | 48 |
| 2071103 | 10 | 2071125 | 48 |
| 2071104 | 46 | 2071125 | 48 |
| 2090020 | 5.35 | 2079272 | 54 |
| 2078153 | 2079272 | 54 | |
| 2078123 | 105 | 20790304 | 126 |
| 2073123 | 20790304 | 126 | |
| 2073124 | 2079303 | 116 | |
| 2079192 | 2079303 | 116 | |
| 2078171 | 2072113 | 240 |
2 Replies
You could use the following formula
=IFERROR(D2-(VLOOKUP(C2,A:B,2,0)),"Product not found")This formula does a vlookup to find the price from your pricing software (col A & B) and then subtracts that number from the salesbook value giving you the difference as well. Anything that has a 0 as the output is a perfect match, while a positive number means that the salesbook value is higher and negative meaning the pricing software values are higher.
Additionally I put in conditional formatting to show colored arrow to display wherever you see a number whether +ve, -ve or 0.
Attached the file for your reference.
