Multiple column lookup or index/match formula

[londa_vba]

Hello
I am trying to accomplish the following:
On active sheet "B", I would like to reference "$B2" and search for where this cell value is found on sheet "A" which will either be in column B or C and every time I would like the result in sheet "A" column D that corresponds the answer should display on sheet B column C. The match needs to be exact and there will be times where D result is blank. I can get it to work when the lookup area is only one column but two columns is stumping me. Sorry if this is confusing.

Sheet A

	Header	Header	Answer I am seeking
	1	1-Y	dog
	2	2-Y	dog
	3	3-Y	cow
	4-RB	4-RB-Y
	5	5-Y	fish
	6-RB	6-RB-Y

Sheet B

	Column to pull reference	Results go here
	1
	3
	4-RB
	6
	1-Y
	4-RB-Y
	5-Y

 

[DanteAmor]

Sheet A:

Libro1
	A	B	C	D
1		Header	Header	Answer I am seeking
2		1	1-Y	dog
3		2	2-Y	dog
4		3	3-Y	cow
5		4-RB	4-RB-Y
6		5	5-Y	fish
7		6-RB	6-RB-Y
8
Sheet A

Sheet B:

Libro1
	A	B	C
1		Column to pull reference	Results go here
2		1	dog
3		3	cow
4		4-RB	0
5		6	Not exists
6		1-Y	dog
7		4-RB-Y	0
8		5-Y	fish
Sheet B

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(VLOOKUP(B2,'Sheet A'!B:D,3,0),IFERROR(VLOOKUP(B2,'Sheet A'!C:D,2,0),"Not exists"))

 

[DanteAmor]

Or this:

=LET(a,B2:B8,d,'Sheet A'!D:D,IFERROR(IFERROR(XLOOKUP(a,'Sheet A'!B:B,d),XLOOKUP(a,'Sheet A'!C:C,d)),"Not exists"))

 

[LogChief]

=LET(a, XLOOKUP(B2:B8,TOCOL(SheetA!B2:C7),VSTACK(SheetA!D2:D7,SheetA!D2:D7),""), IF(a=0,"",a))