formula help

[ANE0709]

Basically I'm trying to find where the number in Sheet 6 B150 is located on Sheet 5 column B, if it matches then concat Sheet 5 G-H. i tried a few different routes but cant seem to pull it together. Maybe you guys can help?

this is the closest ive gotten Sheet 6 H150=IF(VLOOKUP(B150,'Sheet 5'!B:B,1,0),CONCAT('Sheet 5'!G2," ",'Sheet 5'!H2),"")


formula in H on sheet 6

combine notes on sheet 5 column G-H if number in B match.

 

[Fluff]

How about

=TextJoin(" ",,XLOOKUP(B150,'Sheet 5'!B$2:B$5000,'Sheet 5'!G$2:H$5000,"",0))

 

[ANE0709]

How about

=TextJoin(" ",,XLOOKUP(B150,'Sheet 5'!B$2:B$5000,'Sheet 5'!G$2:H$5000,"",0))[/CO
[/QUOTE]

when applied to that single row it works, but not when applied to the rest of the column

 

[Fluff]

In what way?

 

[ANE0709]

In what way?

i copied yours directly into that cell H150 it worked. but when i went up to H2 and applied =TEXTJOIN(" ",,XLOOKUP(B2,'Sheet 5'!B2:B5000,'Sheet 5'!G2:H5000,"",0)) then flash fill down the column nothing populated at all. the formula also changed with each row...

H5=TEXTJOIN(" ",,XLOOKUP(B5,'Sheet 5'!B5:B5003,'Sheet 5'!G5:H5003,"",0))

H100=TEXTJOIN(" ",,XLOOKUP(B100,'Sheet 5'!B100:B5098,'Sheet 5'!G100:H5098,"",0))

 

[Fluff]

You need to put the $ signs back in to stop the row range from changing.

 

[ANE0709]

You need to put the $ signs back in to stop the row range from changing.

i overlooked the $. thanks. that was it.

 

[Fluff]

You're welcome & thanks for the feedback.