Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lookup requiring two values be matched (2003)

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

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Posting Permissions

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