0

I have some particular data in sheet1 and some other data in sheet2.

I need to compare a specific value(Var1) from sheet1 and return a specific value from the sheet2, and return an output for the same. I successfully did that with this formula

=IF(COUNTIF(Sheet2!A:A, A2), "Yes", "No")

Sheet1

+------+------+---------+---------+
| var1 | var2 | result1 | result2 |
+------+------+---------+---------+
|      |      |         |         |
| abc  |  123 | yes     | yes     |
|      |      |         |         |
| bcd  |  234 | yes     | no      |
|      |      |         |         |
| cde  |  456 | no      | blank   |
+------+------+---------+---------+

Sheet2

+-------+-------+-------+
| List1 | List2 | List3 |
+-------+-------+-------+
| abc   |   123 |       |
| bcd   |       | 123   |
| def   |       |       |
+-------+-------+-------+

I need to lookup var1 in list1, and var2 in list2 and list3

I want to compare another value(Var2) in Sheet2, only if the previous result is "Yes", which can be easily achieved with an IF function; but the catch is that it should compare values only from specific rows of Sheet2, which should be plus(+) and minus(-) 2 rows from where Var1 was found in Sheet2.

I am not proficient in VBA, but I have been trying my hand at Excel formulas.

  • Please, consider adding into question a piece of data you have (both for sheet 1 and sheet2). Then, select one specific cell in sheet1 and give 1 example about how the output should be in the end. – Andre Silva Dec 26 '13 at 15:18
  • How many times the value of A2 can appear in Sheet2 ? Also, is it that +/- 2 rows from var1 should be the look_up_range for your 2nd criteria ? – 666bytes Dec 26 '13 at 19:53
  • @AndreSilva Included an example – Pratik Agarwal Dec 27 '13 at 6:30
  • @666bytes A2 is the key of the sheet and is unique in both. – Pratik Agarwal Dec 27 '13 at 6:35
  • @666bytes Yes. +/- 2 rows from var1 should be the look_up_range for your 2nd criteria – Pratik Agarwal Dec 27 '13 at 6:40
0

This was fun to puzzle through :)

Using your example, I placed this function in C2 (under 'result1'), and array-filled it down:

=IF(COUNTIF(Sheet2!A:A,A2),"Yes","No")

And for D2 under 'result2':

=IF(C2="No","No",IF(COUNTIF(INDIRECT("Sheet2!C"&MAX((MATCH(A2,Sheet2!A:A)-2),1)&":C"&(MATCH(A2,Sheet2!A:A)+2)),B2),"Yes","No"))

Again, array fill it down, and it should work :)

|improve this answer|||||
  • Works like a charm!!! Thanks!! Learned a lot!! – Pratik Dec 30 '13 at 8:04
  • Nicely done. I try to avoid indirect when possible, though, since you'll have to update your formula when, say, inserting a column to the left or renaming your worksheet. – maybeWeCouldStealAVan Dec 31 '13 at 14:47
0

You can use OR() and compare if you have plus and minus. Something like this:

=IF(OR(A1="+";A1="-");"Ok";"")

Then you can change "Ok" for you formula.

=IF(OR(A1="+";A1="-");=IF(COUNTIF(Sheet1!C:C, A2), "Yes", "No");"")

Is this what you need?

|improve this answer|||||
  • My question was pretty vague, sorry bout that. Edited it for more clarity. – Pratik Agarwal Dec 27 '13 at 7:37
0

Try: =IF(C2<>"Yes","",IF(COUNTIF(OFFSET(Sheet2!$B$1:$C$5,MAX(0,MATCH(A2,Sheet2!A:A,0)-3),0),B2)>0,"Yes","No"))

I'm assuming that:

  • var1 is a unique key, as you said. Otherwise this will match only the first occurrence.
  • You want to match var2 in both list1 and list2. Otherwise, you should narrow the search window to $B$1:$B$5. If there are more lists to the right, just add columns to the search window.

Let me know if you have any questions about how this works.

|improve this answer|||||
  • Works like a charm!!! Thanks!! Learned a lot!! – Pratik Dec 30 '13 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.