+ Reply to Thread
Results 1 to 3 of 3

OFFSET a RANDomly selected INDEX cell?

  1. #1
    Registered User
    Join Date
    02-10-2008
    Posts
    13

    OFFSET a RANDomly selected INDEX cell?

    Hi,

    I have two arrays that correspond with each other. They each contain the same words in different languages. For example, the first array is A2: D5, and the second array is F2:I5.

    In one cell, A7 for example, I randomly select a word in the first array, using a combination of INDEX and RAND commands. Once the word is RANDomly selected, I'd like another cell, B7 for example, to return the corresponding word in different language.

    In this case, I'd like the word returned in B7 to be an OFFSET of the cell selected by the RAND formula in A7. For example, =OFFSET(???,0,5). That is, if A7 returns the word in C2, I want B7 to return the word in H2. If A7 returns the word in D4, I want B7 to return the word in I4.

    Is there a command to refer to the cell which another cell is referring to? I want B7 to refer to the cell A7 is referring to, and then OFFSET that. Any help is appreciated. Thanks.

    Clark

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Assuming that there are no duplicates in A2:D5 you could try this formula

    =INDEX(F2:I5,MIN(IF(A2:D5=A7,ROW(A2:D5)-ROW(A2)+1)),MIN(IF(A2:D5=A7,COLUMN(A2:D5)-COLUMN(A2)+1)))

    needs to be confirmed with CTRL+SHIFT+ENTER

  3. #3
    Registered User
    Join Date
    02-10-2008
    Posts
    13
    Thanks so much! Works like a charm!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1