Look and return exact match

[ashani]

Hi everyone

I wonder if someone could pls help me - I’m looking for the formula that extract and return exact match albeit it’s a same value. For example :

ABCD 1
FGH 2
ABCD 3
XYZ 2
ABCD 4

I want to look up alphabets and return numbers. I tried using xlookup but as some have same values but with different numbers I’m not getting the correct return

Your help will be much appreciated

Thank you

 

[awoohaw]

Match, VLOOKUP, HLOOKUP only get the first instance and no further.
You may want to consider the FILTER Function which will return multiple records.

=FILTER(A1:B4,A1:A4 = "ABCD","")

(I manually typed that, so it may not be accurate, the online documentation on FILTER is good.)

 

[ashani]

Thank you for your help but it's not working unfortunately

 

[awoohaw]

Not sure what you want. I was thinking this:

Book1
	A	B	C	D	E
1	Target	Return
2	ABCD	1		ABCD	1
3	FGH	2		ABCD	3
4	ABCD	3		ABCD	4
5	XYZ	2
6	ABCD	4
Sheet2

Cell Formulas
Range	Formula
D2:E4	D2	=FILTER(A2:B6,A2:A6="ABCD","")
Dynamic array formulas.

 

[ashani]

Thank you @awoohaw
sorry I think I didn't explain properly, so there's 2 conditions - the top one in Cell C2 is static and Target in A5 onwards are not static - here's the screenshot :

 

[awoohaw]

123 isn't anywere in the matrix. I'm not sure what you want to get from that? Can you elaborate more?
Where are you matching 123?

 

[ashani]

The Target and Return is from formulas, not sure if filter will work as I tried using :

=FILTER(A2:B9,(A2:A6="ABCD")*(b2:B9="123")) and it come up as #N/A

 

[ashani]

123 isn't anywere in the matrix. I'm not sure what you want to get from that? Can you elaborate more?
Where are you matching 123?

Sorry I updated my dataset

 

[awoohaw]

The Target and Return is from formulas, not sure if filter will work as I tried using :

=FILTER(A2:B9,(A2:A6="ABCD")*(b2:B9="123")) and it come up as #N/A

you are missing the 3rd argument of the FUNCTION.

 

[awoohaw]

Book1
	A	B	C	D	E	F	G	H
1	Target 1	ABCD
2	Target 2	123
3
4	Target1	Target2	Return
5	ABCD	123	1		ABCD	123	1
6	FGH	456	2		ABCD	123	4
7	ABCD	789	3
8	XYZ	654	2
9	ABCD	123	4
Sheet2

Cell Formulas
Range	Formula
E5:G6	E5	=FILTER(A5:C9,(A5:A9=B1)*(B5:B9=B2),"")
Dynamic array formulas.