• 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.

Lookup a unique, conditional value between two sheets

M

morph1

New Member
The following problem is probably simple, but I can’t figure out the right lookup or matching solution. Please help:


Sheet 1 has the following:

Column A, B contain non-unique numbers

1,000 rows


Sheet 2 has the following:

Column A, B, C contain non-unique numbers

Column D contains unique text IDs

10,000 rows


Task:

For each row in Sheet 1 get unique text IDs from Sheet 2 that satisfies the following condition:

Sheet 1, Column A = Sheet 2, Column A

Sheet 1, Column B is greater than Sheet 2, Column B but less than Sheet 2, Column C


Thank you.
 
the first question is relatively easy. you can do a vlookup


=vlookup (sheet 1 column A, sheet 2 columns A:D, 4, false)


your second question, however, will create unwanted issue due to the requirement.


You are asking "any" number in second B on sheet 1 is greater than sheet 2 column B, and less than column C of sheet 2. That may still generate multiple possibilities on column D for the ID!
 
Fred,


While Sheet 2 Column B, and Column C contain non-unique numbers the relative relationship between the two columns (Sheet 2 Column B and Column C) is unique, because each Sheet 2 Column B and Column C pair represents a coordinate, which is unique for each Column B and Column C pair.


Please help with a complete formula that solves the complete problem.


Thank you.
 
may be i have mistaken what you are asking for. here is my understanding, assuming the following data are columns B to D on sheet 2.


If you have a figure 3 from column B on sheet 1, and your asking it be bigger than 2 on sheet 2 column B and less than 8 on sheet 2 column C, it will give you two possiblities where the ID in D could be "EE" or "FF".


1 2 AA

1 2 BB

2 2 CC

3 2 DD

3 3 EE

3 3 FF

4 3 GG

4 5 HH

4 5 II

5 5 JJ

5 6 KK

5 6 LL

6 7 MM

6 7 NN

7 8 OO

7 8 PP

7 9 QQ

7 9 RR

7 10 SS

7 10 TT


May be a visualization of your data column can shed more light.
 
Fred,


Yes, almost, except that sheet 2 column B and C would not have the same values in two different rows, the pair is always unique. Please help.


Thank you.
 
You must log in or register to reply here.
Back
Top