I have a list of 4 values in Sheet1 and 4 values in Sheet2. In Sheet3 I will combine a random selection of these numbers and return the value in a column.
(A fifth column in Sheet3 will be used to do calculations with ValueS1 and ValueS2)
Sheet1
| NumberS1 | ValueS1 |
|---|---|
| 1 | 17.10 |
| 2 | 17.20 |
| 3 | 17.12 |
| 4 | 17.15 |
Sheet2
| NumberS2 | ValueS2 |
|---|---|
| 1 | 16.10 |
| 2 | 16.20 |
| 3 | 16.12 |
| 4 | 16.15 |
Sheet3
| NumberS1 | NumberS2 | ValueS1 | ValueS2 |
|---|---|---|---|
| 1 | 3 | 17.10 | 16.12 |
| 2 | 2 | 17.20 | 16.20 |
| 4 | 1 | 17.15 | 16.10 |
| 3 | 4 | 17.12 | 16.15 |
What kind of function can give the desired return?
I have looked into examples using "Indirect" but cannot see how they will solve my problem.
CodePudding user response：
Regular Lookup could do:
=LOOKUP(A2:A5,Sheet1!A2:A5,Sheet1!B2:B5) in Sheet3!C2
And
=LOOKUP(B2:B5,Sheet2!A2:A5,Sheet2!B2:B5) in Sheet3!D2
Or VLOOKUP:
=VLOOKUP(A2:A5,Sheet1!A2:B5,2,0) / =VLOOKUP(B2:B5,Sheet2!A2:B5,2,0)
CodePudding user response：
for the randomization: =ROUNDUP(RAND()*4,0)
rand() gives you a number between 0 and 1, so rand()*4 gives you a number between 0 and 4.
roundup(x,y) round up the number x with y digits you want to round the number up to (in our case 0).
for import the right number from sheet 1 or 2: =VLOOKUP(A1,Sheet1!A1:B2,2,0)
A1 - The value you look for in sheet 1 or 2.
Sheet1!A1:B4 - The array he look for your value on the firs column, always on the first column.
2 - The column you want to import the value from. (because we write an array of tow columns. we can write here only 1 or 2)
0 - it's an Optionally index (0 or 1). o is if you want an exact match of the return value.