where two conditions are met in a row (in two columns via two searches) return that rows specific cell.

stoothom

Board Regular
Joined
Feb 16, 2017
Messages
62
Hi Guys,

Thanks once again. New Job and I didn't know I'd need so much excel.

Basically I need to look at two cells E2, and F2 and where you find both together in a row as part of column A and B then return C. I need it to also be capable of approximate matches.

Example below for reference.

Searching E2 (AA) in column A and F2 (YY) in column B should match 3 rows and return 3 YES's (YES 1, YES 2, YES 3) one after the other in E3-E5. I need it capable of approximate match because of examples like row 5.

The real data is a lot more complicated with over 3000 rows. If you can help me with an easy fill down formula I'd be ever appreciative.

A
B
C
D
E
F
1
Name 1
Name 2
Name 1
Name 2
2
AA
XX
No
Search:
AA
YY
3
AA
YY
YES 1
Result 1:
4
AA
YY
YES 2
Result 2:
5
AA 1
YY 1
YES 3
Result 3:
6
AA
ZZ
No
Result 4:
7
AA
ZZ
No
Result 5:
8
AA
ZZ
No
9
AA
ZZ
No
10
BB
XX
NO
11
BB
YY
NO
12
BB
YY
NO
13
BB
XX
NO

<tbody>
</tbody>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
In E3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$13,SMALL(IF(ISNUMBER(SEARCH($E$2,$A$2:$A$13)*SEARCH($F$2,$B$2:$B$13)),ROW($C$2:$C$13)-ROW($C$2)+1),ROWS($E$3:E3))),"")
 
Upvote 0
Hi,

And first sorry, struggling.

I had your formula working but now I've broke it / think I need something further (realisation).

Now combined with my real data the formula looks like this.

=IFERROR(INDEX(Plan!$E$2:$E$20000,SMALL(IF(ISNUMBER(SEARCH(E2,Plan!$A$2:$A$20000)*SEARCH(F2,Plan!$B$2:$B$20000)),ROW(Plan!$B$2:$B$20000)-ROW(Plan!$B$2)+1),ROWS($E$3:E3))),"")

My source data is in another sheet named Plan.

The search related to E2, (source data in column A) needs be approximate, when it's found these approximate searches I want my formula to only return exact matches via the F2 search, (source data in column B).

I've played around with it but I'm having no luck. It's currently returning the wrong data.

Can you help one weary excel user once more?
 
Upvote 0
Alternatively, is there a way to remove all proceeding spaces before text? Each number of spaces will be different so a quick fill down would be good.

If I can do this then both searches E2 and F2 could then be exact..
 
Upvote 0
So. ( I know lots of messages sorry).

I've trim'd the source data proceeding spaces so I'd like the formula to only return exact matches please..

How do I do this?
 
Upvote 0
So. ( I know lots of messages sorry).

I've trim'd the source data proceeding spaces so I'd like the formula to only return exact matches please..

How do I do this?

Do you mean this?

=IFERROR(INDEX($C$2:$C$13,SMALL(IF(ISNUMBER(SEARCH($E$2,$A$2:$A$13)),IF($B$2:$B$13=$F$2,ROW($C$2:$C$13)-ROW($C$2)+1)),ROWS($E$3:E3))),"")
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top