Results 1 to 6 of 6

20060206, 21:57 #1
 Join Date
 May 2002
 Posts
 413
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20060206, 22:06 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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>

20060206, 22:10 #3
 Join Date
 May 2002
 Posts
 413
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20060206, 22:13 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: lookup requiring two values be matched (2003)
Sorry, I don't understand. Could you provide a detailed example of what you want?

20060207, 13:44 #5
 Join Date
 May 2002
 Posts
 413
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20060207, 14:10 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.