Excel help needed.

On sheet 2 I have two columns that contain numbers.
EG A1 95 .... B1 1318 ..... A2 94.5 ..... B2 1313 ..... and so on.

On sheet 1 I want to type in a number into A1. In B1 I want it to find that number in sheet 2 column A and
give the number that is in column B (same cell number)

So if I type in sheet 1 A1 94.5 the result will be 1313 in sheet 1 B1

Code for sheet 1, B1, assuming your range of sheet 2 data is A1 to B4 (increase the range to suit).

=VLOOKUP(A1,Sheet2!A1:B4,2,FALSE)

Thanks Camie

How would I do it if instead of 2 long column I had 8 shorter ones in sheet 2

eg instead of A1 to 100 & B1 to B100
I have A1 to A25 B1 to B25, C1 to C25 D1 to D25 etc

Break up the formula I gave you.

=VLOOKUP(A1,Sheet2!A1:B4,2,FALSE)

=VLOOKUP(source data cell,data range,column number,FALSE)

The 'FALSE' condition sets it to look for exactly that value. Bear in mind that if there are duplicate values in the first column, Excel will return the first result, e.g. using '4' as the search value:

1
2
3
4 (this is the one returned, and the others are ignored)
4
4
5

That aside if you're looking at A1 to D25, your code would be:

For cell B1 (first sheet):

=VLOOKUP(A1,Sheet2!A1:D25,2,FALSE)

cell C1:

=VLOOKUP(A1,Sheet2!A1:D25,3,FALSE)

cell D1:

=VLOOKUP(A1,Sheet2!A1:D25,4,FALSE)

Edited by camieabz (Sun 12-Feb-12 14:02:36)

Here's a better one:

http://www.camieabz.co.uk/vlookup.xls

Sheet 1 is the 'search' sheet, where you pop the year (note the sheet names), and the week number in. It will return the sales for Tom, Dick and Harry and then sum them (just for fun).

Not to sure I was clear. No new information is added, the information is just moved.

I only want to use cells A1 and B1 in sheet 1
In sheet 2 the information runs from A1 to A100 & B1 to B100

I want to have sheet 2 only going down to row 25, so want to move what is in Cells A26 / B26 down to cell A50/ B50 to cells C1/D1 to C25/ D25 and so on.

I want the result to be the same having a single column

Edited by deleted (Sun 12-Feb-12 14:51:42)

Hi David.

Was just giving an example workbook with some VLOOKUPs included, showing how it can be done. Just adapt some of the VLOOKUPs. The INDIRECTS are for if you want to specify different sheets.

Cheers Camie,
will have a play around later .

No, for some reason that doesn't appear necessary, the OP is trying to break up his 100-long single column-pair into 4 x 25 long logically contiguous column-pairs but still get the same result.

This would seem to over-complicate the problem for no advantage but to make the sheet more "square".

So post up the perfect solution.