Search 2 columns, for MATCH in column in a second sheet, insert value

[davidmyers]

Hi, I have 2 sheets, I want to search / match Sheet2 cols A or B with the value in Sheet3 col. C, if there's a match
I also need both col A & B in Sheet2 to be numeric (neither cell blank), if these criteria are met then Sheet3 col D = "BZ" else blank.
What's the best way to do this?
Any help much appreciated.

 

[awoohaw]

I combined all into one worksheet, you'll have to adjust your references. I also shortened the data strings.
If you have 365 (Please update your profile to show what version you have) there maybe more elegant solutions.
You may need to use the CNTL-SHFT-ENTR keystroke to commit the formula to the cell.


But, I will say this. Your sample data does not really give enough information to test false scenarios. Please test thoroughly.

Book1
	A	B	C	D	E	F	G	H	I
1	Sheet3						Sheet2
2	ID	Complaints & Uses	species-id	BZ			Berc ID	Memp ID	Species Name
3	52837	Imp	2	BZ			805	2	Arbu
4	52835	Lax	2	BZ			903	3	Anet
5	52836	Camin	2	BZ			993	4	Arte
6	52802	Stom	3	BZ			774	5	Capp
7	52805	Influ	3	BZ			834	6	Cera
8	52803	Sever	3	BZ			820	7	Thym
9	52804	Eye	3	BZ				14	Acan
10	52865	Urin	4	BZ			327	15	Achi
11	52869	Hali	4	BZ			761	16	Adia
12	52870	Tooth	4	BZ
13	52854	Heart	4	BZ
14	52855	Vomi	4	BZ
15	52856	Wash	4	BZ
16	52857	Excess	4	BZ
17	52859	Gastro	4	BZ
18	52860	Nausa	4	BZ
Sheet3

Cell Formulas
Range	Formula
D3:D18	D3	=IF(SUM((IFERROR(MATCH($G$3:$G$11,C3,0),0)+IFERROR(MATCH($H$3:$H$11,C3,0),0))*(ROW($G$3:$G$11)-2)), IF(SUM(--(ISNUMBER(INDEX($G$3:$H$11, SUM((IFERROR(MATCH($G$3:$G$11,C3,0),0)+IFERROR(MATCH($H$3:$H$11,C3,0),0))*(ROW($G$3:$G$11)-2)),0)))), "BZ",""),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[davidmyers]

Hi awooha,
Thanks very much for taking the time to write this formula, never realized how complicated it would be, I would never
have arrived to this level. I created 1 sheet, copied your format and changed the formula to include the whole worksheet.
I was unable to upload the bbcode - kept getting error "generated content exceeds limit allowed".
So here's my amended formula to include all G-H rows:

=IF(SUM((IFERROR(D9MATCH($G$3:$G$315,C3,0),0)+IFERROR(MATCH($H$3:$H$315,C3,0),0))*(ROW($G$3:$G$315)-2)),IF(SUM(--(ISNUMBER(INDEX($G$3:$H$315,SUM((IFERROR(MATCH($G$3:$G$315,C3,0),0)+IFERROR(MATCH($H$3:$H$315,C3,0),0))*(ROW($G$3:$G$315)-2)),0)))),"BZ",""),"")

And the rows that illustrate the error:

COL:-------A--------||-------------------B-----------------------||-------------C-----------------||---D-------||---E---||----F----||------G----------||----------H---------------||

I see errors in the results eg. row 150 shows "BZ" in cell D, but row 9 cell G is blank.
Any ideas? Thanks very much for your help.

 

[awoohaw]

Are you saying you tried to upload a xl2bb snippet of your worksheet and you recieved that error? That means you just need to make your selection of cells to post smaller.

As far as the error in 150. what value is in the cell for Berc ID where Memp ID is 14? Is it a calculated "" or a truly blank cell?

 

[davidmyers]

Yes I received the error trying to upload a selection, and the selection was the minimum that included the error I posted (150 rows).
The cell in line 150 Berc ID is not calculated. How can I check if it's a truly blank cell?

 

[awoohaw]

There is a function.

=ISBLANK(cellreference)

of course, the formula i suggested could be wrong. As that scenario was not tested.

 

[davidmyers]

When I try ISBLANK in the cell I receive a "0" before checking its blank.
Could you try ISNUMERIC and > 0?
Also if the first check is if both fields G & H are numeric and > 0 then look for a MATCH?
Sorry I don't feel qualified to do this myself