Help with vlookup formula

S

sarahp

Hi,

I have two separates excel worksheets.

Worksheet no 1 which has two columns : A ( XP numbers) and B ( DO
numbers)

Worksheet no 2 which has several columns : A ( XP numbers) to O

PS: both worksheet have column A in common ( XP numbers)

I am trying to extract the data from the column B ( DOI numbers ) o
worksheet no 1 and insert them into worksheet no 2 ONLY when the dat
from column A ( XP numbers) match !

Can anyone help me with the formula?
Step by step ...please.

Many thanks

Sara
 
J

JE McGimpsey

One way:

In Worksheet 2, cell B1:

=IF(ISNA(MATCH(A1,Sheet1!A:A,FALSE)), "",
VLOOKUP(A1,Sheet1!A:B,2,FALSE))
 
J

Jon Quixley

Sarah,

On a nerw workbook set up some random numbers in sheet 1 cells A4 to
A17 and in column B enter some numbers occasionally in cells B4 through
to B17 (don't fill all of them). In Sheet two copy column A from sheet
one to two and copy the formula below into cell B4 and copy this down
to b17.

You will need to adapt the formula (at the bottom) a little to fit your
needs, but basically this it it

So what does it all do? - Well, you have two formulae sitting one
inside the other. The VLOOKUP(a4,sheet1!$a$4:$b$17,2,true) part does
the following things: The formula instructs the cell to look at cell A4
(this is on sheet 2) and then go to a block of data on sheet 1 between
cells a4 and b17 - it then reads down the first column until it finds a
number that matches the number it has found in a4 on sheet2. Having
found this number it goes 2 columns over - that is to column B and
returns the number that it finds there.

The next part of the formula sets up a filter to place a blank in
column B on sheet 2 if it can't find a number or the number is zero in
column B on sheet 1 - This is the first part where it says
=IF(+VLOOKUP(A4,Sheet1!A4:B17,2,TRUE)=0," ". If the number is more than
zero then the second part kicks in and you get a number in column B in
sheet 2 that matches its mate on sheet 1
If you are having trouble with this once you've set the whole thing up,
try clicking on the fx symbol next to the formula bar and it should tell
you what the formula should be doing

Good luck!
Jon

This part you copy and paste:
=IF(+VLOOKUP(A4,Sheet1!$A$4:$B$17,2,TRUE)=0,"
",+VLOOKUP(A4,Sheet1!$A$4:$B$17,2,TRUE))
 
Top