Random selection of data that meets criteria in text string

Hi,

I have a large table of data from which I am pulling out random selections.
My first random selection is random-random for which I am using =INDEX($G$67:$G$1092,ROUND(RAND()*COUNTA($G$67:$G$1092),0))
This works perfectly.
Column G has a concatenate of 3 values, vendor+purchase order+invoice

My next selection has to be a random selection of the vendor.
I dont want to be pulling the data into another table, I'd love to be able to get a singular formular to do this.
I have tried inputting Countif instead of count A and using the "*"&"vendor"&"*" - this does not work.
I have also tried nesting the Match function in various locations but this also is not working.
The RandBetween function only works with numerical values as far as I can understand.

Does anyone have any suggestions - all the posts I have read only seem to pertain to numerical values.

Help would be very much appreciated.

Do you want to 'COUNTIF' vendor as a word?

Hi Istvan,

I have tried using CountIf and whilst it keeps the fomula valid, it wont pull the correct vendor. Perhaps I am doing something wrong here..?

Hi Kaper,

I will try your suggestion - thank you for putting this one together.

I have put together a little demo of what Im trying to achieve - essentially in the yellow box I would like a random selection from column "A" that contains "Intel".
I need to use the concatenate as this is how I will get a unique value populated in the yellow box.

Thank you again

Thank you Siva.
This solution works absolutely perfectly.

Originally Posted by SerenaJoselyn

Hi Istvan,

I have tried using CountIf and whilst it keeps the fomula valid, it wont pull the correct vendor. Perhaps I am doing something wrong here..?

For example, if you want ’COUNTIF’ „table” in A1:

=COUNTIF(A1,"*table*")