if cell range contains certain text then return value of corresponding cell

[krajivgandhi]

Hi
Appreciate if someone could help me on the below part
I have three columns A,B,C
A1-Jack
A2-Jill
A3-Jack
A4-Rose
A5-Jack

B1-Pass
B2-Pass
B3-Fail
B4-Pass
B5-Pass

C1-Date1
C2-Date2
C3-Date3
C4-Date4
C5-Date5

So my requirement is if A:A = Jack and B:B = Pass then it should return value as below column d
D1-Date1
D2-Date5

[Glenn Kennedy]

Try:

=IFERROR(INDEX($C$1:$C$5,SMALL(IF($A$1:$A$5="Jack",IF($B$1:$B$5="Pass",ROW($A$1:$A$5))),ROWS(D$1:D1))),"")

entered as an array formula. ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[krajivgandhi]

Thanks a lot Glenn.
Almost there. But I am getting output as
D1-Date1
D2-Date1
D3-Date1
D4-Date1
D5-Date1

Instead of

D1-Date1
D2-Date5

Thanks in advance

[shukla.ankur281190]

Confirm the posted #2 formula with control+Shift+Enter not just enter

[krajivgandhi]

yes indeed. I pasted formula with ctrl+shift+enter and then I got output as
D1-Date1
D2-Date1
D3-Date1
D4-Date1
D5-Date1

Instead of

D1-Date1
D2-Date5

[Glenn Kennedy]

Wrong way to do it. Copy paste and CTRL-SHIFT-ENTER in ONE cell and then copy down.

[krajivgandhi]

wow. genius. Problem solved. Thanks a lot

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[krajivgandhi]

Hi, when I change C1 or C2 or C3 or C4, D1 and D2 does not change. Is it like each time I need to press ctrl+shift+enter in D1 and then copy down?

[samba_ravi]

I think your calculation option is in manual mode change it to Automatic Mode

Formula > Calculation Options >Automatic
or can use pivot table simply

[krajivgandhi]

It is already in Automatic mode. Can't use pivot. setting up formulas to avoid pivot

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[krajivgandhi]

It contains full of Banking related confidential details. I will try to create one dummy and send. Thanks

[samba_ravi]

Attach sample excel file after removing the confidential dataa

[krajivgandhi]

Hi, I have attached sample excel which is replica of original.
Requirement - which contains date wise tab. The last tab which has to be automated. This last tab pulls data from all other tab and count of who did how much work with count. Thanks in advance

[Glenn Kennedy]

you have merged cells all over the place. Their occurrence follows no logical pattern. Merged cells are a nightmare to cope with. I strongly suggest that you reconsider the format of the last sheet and get rid of ALL of the merged cells. I am certainly not going to spend hours trying to come up with a work-around.

[krajivgandhi]

Glenn, appreciate if you could make merged cells into normal and provide me a solution.

[Glenn Kennedy]

Take a look at this. there's a lot going on. I will not attempt to explain any of until you are OK with the approach taken. Take a look and see if it is close enough to what you need.

You will have to enable macros on opening (an old inbuilt Excel macro has been used to return a dynamic list of sheet names).

You will quickly see that your original example in Post 1 was totally unrepresentative of your posted data. I just hope that the latest version is REALLY representative of what you are really using...

Also... your example was an .xlsx file (excel 2007 and later), but your profile shows Excel 2003. Which do you use? Please amend your profile appropriately.

Also, also: some of your column headers were inconsistent (corrected, but currently shaded orange). You need to ensure 100% consistency.

[krajivgandhi]

Glenn, tremendous work. I have no words to express my gratitude. This is what I was exactly looking for. I will float the real data and get back for help
Sorry about the confusions in excel and in my profile. Will update my profile. Thanks a lottt.

[Glenn Kennedy]

Hopefully it'll be OK on your real data: The summary rows are done more-or-less manually. Providing the number of individuals isn't too great and doesn't change too much, it should be OK.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.