# Thread: lookup requiring two values be matched (2003)

1. ## lookup requiring two values be matched (2003)

I would like to do a double lookup. Using the data below as an example, I would like to replace the C value when the A value is between the range A<1652500 and A>1672500 and the B value is between the range B<10432500 and B>10455000. My lookup table would be structured similarly. The VLOOKUP needs to match both the A and B values.

Thanks.

A B C
1640000 10420000 -0.016711554
1665100 10435000 -0.016802216
1665200 10435100 -0.016893296
1675000 10458000 -0.016984795

2. ## Re: lookup requiring two values be matched (2003)

1) Replace the C value with what?
2) A<1652500 and A>1672500 doesn't make sense. Do you mean A>1652500 and A<1672500 ?

<img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

3. ## Re: lookup requiring two values be matched (2003)

If the A values are between 1652500 and 1672500 and the B values are between 10432500 and 10455000, I would like to replace the C value from a lookup table such as shown below. I hope this 'splains it better. Thanks.

1652500 10432500 -0.139699926
1652600 10432500 -0.164017775
1652700 10432500 -0.19023345
1652800 10432500 -0.21129775
1652900 10432500 -0.222020998

4. ## Re: lookup requiring two values be matched (2003)

Sorry, I don't understand. Could you provide a detailed example of what you want?

5. ## Re: lookup requiring two values be matched (2003)

Let me try again.

I have x,y,z data on a 100 ft. x and y grid spacing. I have one data set which goes from X(min) = 1,640,000 to X(max) = 1,690,000 and Y(min) = 10,420,000 and Y(max) = 10,470,000. The data are ordered as an ASCII file as x, y, z with the x data going from min to max for each y value, min to max

I have second data set which goes from A(min) = 1,652,500 to A(max) = 1,672,500 and C(min) = 10,432,500 and C(max) = 10,455,000. The data are also ordered as an ASCII file as x, y, z with the x data going from min to max for each y value.

Each data set has a unique combination of x,y values, with each x,y in the second data set having a match with the first data set.

For each x,y in the first data set which matches an x,y in the second data set, I would like to replace the z value in the first data set with the z value from the second data set. For example:

Data Set 1
X Y Z
1640000 10420000 -0.016711554
1665100 10420000 -0.016802216
1665100 10435000 -0.016802216
1665200 10435000 -0.016893296
1675200 10458000 -0.016984795

Data Set 2
A B C
1665000 10435000 -0.9999
1665100 10435000 -0.8888
1665200 10435000 -0.7777
1675300 10435000 -0.6666

In data set 1:
the 1st line: X < A(min) and y < B(min), so the Z value would remain unchanged.
the 2nd line: X = A(2), but y < B(min), so the Z value would remain unchanged.
the 3rd line: X = A(2) and y =A(2) so the Z value would be replaced with the 2nd data set C value of -0.8888.
the 4th line: X = A(3) and y =A(3) so the Z value would be replaced with the 2nd data set C value of -0.7777.
the 5th line: X= A(3), but Y > B(max), so the Z value would not be replaced

conceptually my lookup statement would be something like:
= if(X,Y pair values match A,B pair values then insert C value from A,B pair match, else insert Z value from X,Y pair)

Thanks for the help.

John

6. ## Re: lookup requiring two values be matched (2003)

Thanks, I couldn't have guessed that from your first two posts.

See the attached workbook.
- I added concatenated X & Y values in column D.
- I used these in the MATCH formula in column E to lookup both X and Y in one go.
- In column F, the original Z is returned if column E contains #N/A, otherwise the lookup value from the other table.
- You could now use Copy and Paste Special > Values to replace the original Z values.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•