Forum Discussion
Need Excel functionality for the random sequence Vlookup
Hi Expert guys,
I need Excel functionality with the below data.
1st table contains the below data.
1) 1st table.
2nd table(as below data) - 1st column contains the random array arrangement of the 1st table values of 1st column.
Expecting exact corresponding respective values of 2nd table - 1st column results in the 2nd table - 2nd column through =VLOOKUP formula or any other possibility in excel from the values of the 1st table - 2nd column.
2) 2nd table
I need to distribute the similar kind of data in my works.
Request you to suggest/ help in this.
Thank you in advance for your help.
=INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=A9,ROW($A$2:$A$6)-1),COUNTIF($A$9:A9,A9)))Those who have e.g. Excel 2013 can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- Thank you, I have tried this formula, it's working.
See the attached workbook. I added a helper column to the raw data with RAND() formulas.
The 2nd table uses INDEX and SORT to create a list in random order.
Press F9 to create a new randomized result.
- Got it, but I see the error in column 1 and 2 as below.
Request you help here.
Column 1 Column 2 Helper Column 1 Column 2
a 1 0.143551864 #NAME? #NAME?
a 2 0.736334881 #NAME? #NAME?
b 3 0.603049822 #NAME? #NAME?
c 4 0.611444202 #NAME? #NAME?
a 5 0.475457091 #NAME? #NAME?
Based on the sample you provided that could be:
in F3:
=INDEX( FILTER(Table1[Number],Table1[Letter]=[@Letter]), COUNTIF(E$3:E3,E3) )- Thank you, I tried it in Microsoft 365 web. It's working.
It might help us help you if you could explain the bigger picture here. Is it possible to tell us what the real data are about? If not, still would help if we understood more....
I say that because in many ways your first table looks quite random, so one wonders (this one does, at any rate) why not just make everything random altogether?
If you can't tell us more about the context itself, let me ask these:
- I notice that even though the series of letters has changed "randomly," the sequence of values connected with "a" is still the same as in "Raw Data" -- i.e., 1,2,5. Is that essential, or could those values be randomly sequenced, e.g., 2, 5, 1?
- wouldn't it be more random if those values associated with "a" were randomly sequenced?
- How many rows are there in your real tables?
