0

Ok, so here are the steps that I would like to create a formula for:

  1. Check whether the value in a specific cell appears in a column on another sheet eg: MATCH(A3,'Sheet1'!$A:$A)
  2. If yes, then check the value in Sheet1 column I on the same row as that value was found
  3. If the value in column I matches 'specified value' then return the value B3 from Sheet1
  4. If the value in column I doesn't match then leave cell blank

The bit I'm struggling with is that I need to only return data from rows with the matching value in column A AND the specified value in column I, but there are multiple occurrences of both values in both columns (but hopefully only one occurrence of both values in a single row).

These are the closest 2 formulas I have figured out so far (currently returning no instead of blank if false for testing): =IF(AND('Sheet1'!$A:$A=A2,'Sheet1'!$I[]="WEEK 1"),'Sheet1'!$B[],"NO")

=IF(AND(MATCH(A5,'Sheet1'!$A:$A),'Sheet1'!$I[]="WEEK 1"),'Sheet1'!$B[],"NO")

But the 'I' can't be a fixed value because I don't know what row I am looking at until I have matched the A value.

To put it another way: I need to find out if there is a row in the data tracking sheet where the value in column A = X AND the value in column I = Y, and then return the value in column B.

Hopefully that makes sense. Any help would be very much appreciated.

Improve this question
1

2 Answers 2

Reset to default
1

Your setup is a bit unclear, but this may do what you want:

=IF(INDEX(I:I,MATCH(A3,Sheet1!A:A,0))="Specified Value",B3,"")
Improve this answer
3
  • Hi, thanks for this! This works, except that I need to return the value for 'Sheet1!B:B instead of B3 because I've realised that the B value also changes and when I put that it gives me a Vlookup error. Do you have any idea why that might be? Thanks so much for your help.
    – Gale
    Commented Jul 10, 2021 at 11:20
  • Also, I've just found that when I input a different specified value it doesn't work, even though I've checked the reference sheet and that should return a positive result.
    – Gale
    Commented Jul 10, 2021 at 12:07
  • Actually, I think I've cracked it based on your suggestion to use index, thanks! =INDEX('Sheet1'!A:I,MATCH(1,('Sheet1'!A:A=A6)*('Sheet1'!I:I=$H$1),0),2) The only problem I have now is how to figure out whether there is any way to flag up duplicates... Any suggestions welcome but thanks so much for your help
    – Gale
    Commented Jul 10, 2021 at 14:02
0

You said to compare the value of column I in the same row as the matched value (A5), but why in this function the value of matching A5 compares the value of I6 and returns the value of B6. =IF(AND(MATCH(A5,'Data Tracking'!$A:$A),'Data Tracking'!$I6="WEEK 1"),'Data Tracking'!$B6,"NO")

Whatever, according to the above formula you provided, I found that you only need to add a 0 attribute to the end of the MATCH function.

=IF(AND(MATCH(A3,'Data Tracking'!$A:$A,0),'Data Tracking'!$I3="WEEK1"),'Data Tracking'!$B3,"NO")
Improve this answer
1
  • The problem with that formula is that I don't know what row I'm looking at for the I value on the reference sheet. I need to find a row in my reference sheet where the value in column A matches the reference cell and the value in column I matches the specified value, but I don't know what row that is for either value. So I don't know that it's I6 (for example). That's why my formula doesn't work, because ColumnI needs to be a fixed reference and it isn't.
    – Gale
    Commented Jul 10, 2021 at 11:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .