Need a spreadsheet god, excel help please!!!

Home Forum Chat Forum Need a spreadsheet god, excel help please!!!

Viewing 17 posts - 1 through 17 (of 17 total)
  • Need a spreadsheet god, excel help please!!!
  • clubber
    Member

    vlookup will do it.

    Premier Icon annebr
    Subscriber
    Premier Icon mintimperial
    Subscriber

    =VLOOKUP(A1,’Spreadsheet 2!A:C’,3,FALSE)

    Copy that into column C on Spreadsheet 1 and copy down, you’ll need to play around with the references a bit. The ‘FALSE’ at the end ensures that you only get exact matches or a ‘#N/A’ error so you don’t get incorrect values.

    WackoAK
    Member

    vlookup function will do that

    Premier Icon scaredypants
    Subscriber

    shove over – here I am !

    do you want error bars on anything ?

    irelanst
    Member

    Vlookup will maintain a link between the two spreadsheets, if you want the data imported into spreadsheet 1 so that it becomes independent of spreadsheet 2, then you need to add another step.

    Do the vlookup as mentioned above, then select the column with the vlookup values and “copy” then “paste special”>”values”.

    scruff
    Member

    another question if you dont mind me hijacking. Can you hide rows depandant on values in a certain column?

    organic355
    Member

    Think I am almost there, but not quite.

    have input =VLOOKUP(A3,Sheet1!A2:A384,1,FALSE)

    I put both spreadheets into same work, one tab called mater list another called sheet 1

    but what is getting returned in that box is the ID number above.

    Is the A3 in that formula refering to A3 on sheet 1, or A3 on the sheet i have the formula.

    dont think Ive got my head around how Vlookup works

    I think it is, look up the value of A3 on this spreadsheet, on another spreadsheet called sheet 1, in the range A2 to A384??, not sure what the 1 does?, i understand the false bit.

    I want the function in column J of “master spraedsheet” to look at the value in column A of master spreadsheet, look this up in sheet 1 and return the value that is in column 4 of the corresponding row.

    clubber
    Member

    A3 is the cell on the same sheet as you’re putting the formula that you’re matching to your second spreadsheet table (A2:A384)

    Change A2:A384 to A2:D384 (if you’ve got four columns of data) (or better A:D so that it’s just the columns)

    Change the ‘1’ immediately before the ‘false’ to the column number wihh the value you want to return (4 I think)

    clubber
    Member

    =VLOOKUP(A3,Sheet1!A:D,4,FALSE)

    organic355
    Member

    AWESOME, GOT IT!!!!!! 😀

    THANK YOU!!!! YOU CAN ALL HAVE A VIRTUAL PINT!!!!

    organic355
    Member

    Need a spreadsheet god please!!

    Ive got one excel spreadsheet with 1st 2 columns with….. non sequential numbers and names are actual names rather than A, B , C etc.

    ID No Name
    1 Property A
    2 Property B
    4 Property C
    5 Property D
    6 Property E
    7 Property F
    8 Property G
    9 Property H
    12 Property I
    15 Property J
    20 Property M

    and another spreadsheet with…
    ID Name Person
    2 Property B PERSON A
    4 Property C PERSON C
    5 Property D PERSON A
    9 Property H PERSON B
    12 Property I PERSON A
    20 Property M PERSON C

    How can I effortlessly import column 3 of spreadsheet 2 into spreadsheet 1, short of copy pasting individually?

    edit: damn tabs didnt work.

    organic355
    Member

    Any idea how you use vlookup to search several sheets within a workbook?

    is this possible with VLOOKUP?

    clubber
    Member

    Not with vlookup. The data needs to be a single range

    Premier Icon matthewjb
    Subscriber

    Any idea how you use vlookup to search several sheets within a workbook?

    You could use a IF statement with multiple VLOOKUPs. Depends how the data is organised. Are you looking for someone on three different lists?

    organic355
    Member

    Looking to simply look at 1 workbook which has multiple, see if any of these have a single I’d number on them and return either that number to another spreadsheet, or simply put an x in a box.

    Premier Icon matthewjb
    Subscriber

    OK

    So you already know how to use VLOOKUP. If VLOOKUP can’t find the value it returns an error which will cause problems, so try this:

    IF( ISERROR(‘Your lookup formula’),”Not Found”,”Your lookup formula’)

    This will return ‘Not Found’ if they’re not on the list and the ID if they are.

    You can expand this to multiple lists by nesting another IF statement inside the first. Simply replace the “Not Found” bit with a copy of the whole IF statement that now looks at the second list.

Viewing 17 posts - 1 through 17 (of 17 total)

The topic ‘Need a spreadsheet god, excel help please!!!’ is closed to new replies.