# Excel..nested vlookup possibly..looking for help

Copper Contributor

# 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

# Re: Excel..nested vlookup possibly..looking for help

use this formula next to you data

``=IFERROR(IF(VLOOKUP(C2,A:B,2,FALSE)=D2,"Match","Mismatch"),"Product Code Not Found!")``

Match = represents the prices are the same,
Mismatch = there is discrepancies on the prices

# Re: Excel..nested vlookup possibly..looking for help

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.