+ Reply to Thread
Results 1 to 4 of 4

LOOKUP - referencing cells

  1. #1
    Registered User
    Join Date
    02-14-2008
    Posts
    25

    LOOKUP - referencing cells

    Hi,

    I'm trying to return a value from a worksheet dependant on a value in a cell. The problem is (as I understand it) I can't use a VLOOKUP because the lookup value is not in the first column.

    Here's kinda what my data looks like:

    SHEET-1
    A B C D - (columns)
    1 2 ? ?
    5 6 ? ?

    SHEET-2
    A B C D - (columns)
    T 7 H P
    R 2 F Z
    L 9 V A
    P 6 X W

    I am trying to put a lookup in Cols C and D/Sh 1 which checks the value in Col B/Sh 1 against the value in Col B/Sh 2 and returns the values of Cols C and D in Sheet 2.

    For example; if I looked up the value of cell B1 in Sheet 1 (i.e. the number 2) it would lookup the values in Cols C and D on Sheet 2 and return F and Z in columns C and D. If I looked up 6 (cell B2, Sheet 1) it would return X and W in C3 and C4 in Sheet 1.

    Is there a smart way to do this?

    Thanks in advance.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    In C1 and copy across and down as necessary:

    =VLOOKUP($B1,Sheet2!$B$1:$D$4,COLUMN()-1,FALSE)

    You can use VLOOKUP because your lookup value is in the first column of the table in sheet2 - you can ignore the first column in sheet2.

  3. #3
    Registered User
    Join Date
    02-14-2008
    Posts
    25
    Hi,

    Thaks for the repsonse. I'm still having trouble making it work though. I've transposed the formula you posted into my sheet like so:

    =VLOOKUP($C3,Proximates!$B$1:$D$4,COLUMN()-1,FALSE)

    One thing I've noticed is that in column B on sheet 2 the numbers have a ' in front of them i.e. '50-178. Would this screw with the lookup? I am a bit of an Excel n0ob so sorry if that's a dumb question.

    You can use VLOOKUP because your lookup value is in the first column of the table in sheet2 - you can ignore the first column in sheet2.
    Also, could you explain how this is true? The lookup value is in column B and the things I want returned are in C and D. Nothing's in column A that I'm working with.

    I've only just started using VLOOKUPs so I'm still pretty bewildered by them. They're pretty funky but make my brain twitch whenever I try to do something fancy with them.

    Cheers

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    The solution was based on what you presented in the first post so perhaps you could attach a small sample of your actual workbook. The single speech marks indicate text.

    From Excel help (bold added):
    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

    Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

    Col_index_num is the column number in table_array from which the matching value must be returned.

    Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

+ 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