-1

I have two excel spreadsheets. One is a list of our products(spreadsheet 1) with details, the other is a list of products but with empty fields (spreadsheet 2).

I want to lookup a cell (A1) in spread sheet 2 - find the corresponding value in a row in spreadsheet 1 and then get another cell on the same row and copy it over to the spreadsheet 2.

so search for a value "cars" in one spreadsheet and read off its the number plate in the other spreadsheet.

Is this possible in excel ? I have 1555 rows to lookup and read off for a range of values, i can copy both into one spreadsheet but 2 worksheets

Andrew
  • 343
  • 1
  • 3
  • 9
  • Have a look at the [VLOOKUP function](https://support.office.microsoft.com/en-us/article/VLOOKUP-function-adceda66-30de-4f26-923b-7257939faa65). –  Jan 26 '15 at 09:41
  • THANKS for the help ! I am checking it out now – Andrew Jan 26 '15 at 09:49

1 Answers1

0

Try VLOOKUP()

It does not matter if Your work is in two sheets of the same workbook or two separate excel files.

You use it like this: You put this formula in the empty cells in the second file =VLOOKUP(KEY, TABLE_RANGE, COLUMN_INDEX, 0)

KEY is in your example the cell were "car" value is in the second file TABLE_RANGE is area containing both: KEY equivalent in original data and result You want to copy over. IMPORTANT! first column of range have to be the one that contains KEY values. Also remember to use absolute references ($A$1:$C$55 for example)

COLUMN_INDEX is number indicating column in selected range that You want to use as result

In Your example last parameter have to be 0 (it indicates if Your want exact result or not - it is useful to put 1 in there when You are looking not for exact values but ranges of values)

Than You copy over the formula for all rows You want.

Take a look at this image to visualize how it works and how formula should be used: VLOOKUP
(source: microsofttraining.net)

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Tetlanesh
  • 403
  • 5
  • 9
  • cool, that is what I am looking for but I don't have the syntax right. =VLOOKUP(A2,Sheet1,P2:P1666) I was trying to look up A2 in the first sheet from range p2 to p1666 but its an Name error...Also I want to read off a corresponding cell which is in R (two to the right) – Andrew Jan 26 '15 at 10:37
  • In your example the formula should be: =VLOOKUP(A2,Sheet1!$P$2:$R$1666, 3, 0) Notice that range have been expanded to contain column R. Number 3 is number of column in range (P=1, Q=2, R=3) and always end with 0 as 4th parameter – Tetlanesh Jan 26 '15 at 10:41
  • thats almost it !! just, I am searching for P in Sheet 1 (for A in the second sheet) and copy across result R – Andrew Jan 26 '15 at 10:47
  • I'm not sure I follow. You have two sheets: Sheet1 and (for example) Sheet2, right? Now in Sheet1 contains 1666 rows of data from column P to R, where P is the KEY (value common in both sheets that we use to search) and You want result from column R, right? Now In Sheet2 You have data in column A that corresponds to column P from Sheet1 and You want to get Column R from Sheet1 into Column (for example) B in Sheet2, right? – Tetlanesh Jan 26 '15 at 10:56
  • Also: does those two are in the same file (worksheet) or not? – Tetlanesh Jan 26 '15 at 10:58
  • Yes sheet one has 1666 rows of data, it has two columns of interest: P(SKU) and R( Gtin). I am searching column A from Sheet two - looking P in Sheet one and copying the corresponding value in R(the gtin) to the second sheet. Its one excel file with two sheets. – Andrew Jan 26 '15 at 11:15
  • Ok then, the formula should be put into **B2** cell in **Sheet2** and should be exactly what I wrote before: **=VLOOKUP(A2,Sheet1!$P$2:$R$1666, 3, 0)** and than copied over to all other rows with data in Sheet2 – Tetlanesh Jan 26 '15 at 11:18
  • I've added picture to my answer above that should help out visualize how VLOOKUP is used. Notice that last parameter in this picture is FALSE instead of 0, but those two are equal in excel. – Tetlanesh Jan 26 '15 at 11:27
  • thanks very much for the help !! its a great job :) – Andrew Jan 26 '15 at 14:51