Search value in a range and return another value on the same row

[JoshJ86]

Hi There,

I cant figure this out how to get this to work. I have searched and tried so many existing solutions with no success. Any help would be much appreciated!

Basically, what I want is populate the reference from column B to column K, by finding the value from column J in the range C2:H5.

 

[Micron]

An example of the desired result will often be much more useful than a description alone. The post title suggests you want a value from some other cell in the same row. The description suggests you want the cell address, in which case K2 would contain "B2". Or would it be "C2"? Or maybe I'm that guy at "darts and balloons booth" at the county fair, and so far, no 'pop'.

 

[JoshJ86]

Sorry I find these problems hard to explain. I hope this helps.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

See if this does what you want.
I have assumed that there will not be duplicates in C2:H5.
Also, it is hard to tell if your values are Text values or actual Numbers.
My formula in the mini sheet below will return the Reference values as Text. If they need to be Numbers then try this instead.

=IFERROR(--CONCAT(IF(C$2:H$5=J2,B$2:B$5,"")),"")

24 12 05.xlsm
	B	C	D	E	F	G	H	I	J	K
1	Ref
2	2001247165	2001234365	2001240743	2001250908	2001242333	3001242333	5555555555		2001249404	2001233353
3	2001247633	2001244006	2001240610	2001247067	2001232388	2001232389	5555555556		2001232388	2001247633
4	2001246607	2001232012	2001236022	2001237586	4001234914	2001230854	5555555557		2001240610	2001247633
5	2001233353	2001249404	2001234914	2001236477	2001249405	2001246854	5555555558		2001236477	2001233353
6									2001242333	2001247165
7									2001236022	2001246607
8									7001242333
9									2001250908	2001247165
10									2001240743	2001247165
11									2001246854	2001233353
12									2001232388	2001247633
13									2001249404	2001233353
14									7777777777
15									2001244006	2001247633
16									2001234914	2001233353
17									2001232012	2001246607
18									2001237586	2001246607
19									2001234914	2001233353
20									2001247067	2001247633
21									2001234365	2001247165
22									5555555556	2001247633
23
Reference

Cell Formulas
Range	Formula
K2:K22	K2	=CONCAT(IF(C$2:H$5=J2,B$2:B$5,""))

 

[kvsrinivasamurthy]

Try. In K2

=LET(a,B2:B5,b,C2:H5,BYROW(J2:J25,LAMBDA(ro,IFERROR(INDEX(a,SMALL(IF(b=ro,ROW(b),""),1)-ROW(A1)),""))))

 

[JoshJ86]

=IFERROR(--CONCAT(IF(C$2:H$5=J2,B$2:B$5,"")),"")

Hi Peter, thank you so much for taking the time and manually entering in the data to figure this out for me! You are a life saver, and I really appreciate it. This was my first post and if I ever post again, I will 100% figure out how get and use XL2BB to make everything simple and easy. Thanks again!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.