• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Doing a lookup from table with a value range.

tricoachmartin

New Member
I need to compare two tables to see if they have the same value for the same codes. One table has the codes expressed in a range (one column with the first code of the series and the second column with the last coed in the series). In the other table every code is listed individually. How can I find the codes that don't match? I have put a simplified version of the table below noting code values that I would want the formula to find. Thank you in advance.


Table 1

Beg Code | End Code | Price

1 | 5 | $15.00

6| 6 | $20.00

7| 8 | $5.00

9| 11 | $3.00

12 | 12 | $8.00

13 | 13 | $25.00

14 | 20 | $10.00


Table 2

Code |Price

1 |$15.00

3 |$15.00

4 |$17.00 - PRICE IS DIFFERENT

6 |$20.00

9 |$3.00

10 |$3.00

11 |$3.00

12 |$10.00 - PRICE IS DIFFERENT

13 |$25.00

15 |$10.00

16 |$10.00

17 |$10.00
 

Hui

Excel Ninja
Staff member
Tricoachmartin,


Assuming that Table 1 is in Cell A1 and has range names as titled

and that Table 2 is directly below it


I would do something like the following in the C12 (C12 is next to the 1 $15.00 price


=+IF(OFFSET($C$1,MATCH(A12,Beg_Code),0)<>B12,"Different",B12)
 
Top