MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
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.
[b]ID No Name[/b]
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...
[b]ID Name Person [/b]
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.
vlookup will do it.
vlookup
=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.
vlookup function will do that
shove over - here I am !
do you want error bars on anything ?
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".
another question if you dont mind me hijacking. Can you hide rows depandant on values in a certain column?
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.
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)
=VLOOKUP(A3,Sheet1!A:D,4,FALSE)
AWESOME, GOT IT!!!!!! 😀
THANK YOU!!!! YOU CAN ALL HAVE A VIRTUAL PINT!!!!
Any idea how you use vlookup to search several sheets within a workbook?
is this possible with VLOOKUP?
Not with vlookup. The data needs to be a single range
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?
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.
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. [i]Simply[/i] replace the "Not Found" bit with a copy of the whole IF statement that now looks at the second list.