Matching Fields in Excel 1

[zircom]

I want to compare cells in differnet sheets to match the content and then copy a field into the other sheet if true.

Sheet 1 Sheet 2

Job No Name Job No Name

123 123

456 789

If job number in sheet 1 matches job number in sheet 2 then copy Name form sheet 2 to sheet 1, if not match leave blank.


Any help would be appreciated.

 

[djmsenior]

Not quite sure what you mean but assuming:-

Sheet1
A B C
1 Job No Name
2 123 Bert =(Sheet2!A2=Sheet1!A2,Sheet2!B2,"")
3 456 Ernie

Sheet2
A B C
1 Job No Name
2 123 Bert
3 567 Fred

Hope this helps...

Did you hear about the dyslexic, agnostic, insomniac - he laid awake all night wondering if there really was a dog.

 

[KenWright]

Take a look at the VLOOKUP function - this will do exactly that for you:-

Example syntax:-

Assuming you are on say Sheet1 in say cell B1 and you want to lookup the value of what is in cell A1 on sheet1 in a 2-column table on sheet2, where that value can be found in the first column of that table, and then and return the associated name from the second column of that table.

=VLOOKUP(A1,Sheet2!$A$1:$B$5,2,0)

See my FAQ on this for a more detailed explanation:-

faq68-4743

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi zircom,

If your data are in columns A and B of each sheet, then this in B2 (and copied down) should do it ..

[blue][tt] =IF(ISNA(VLOOKUP(A2,Sheet2!$A:$B,2,FALSE)),"",VLOOKUP(A2,Sheet2!$A:$B,2,FALSE))[/tt][/blue]


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[zircom]

Thanks.

I am after comparing a column on sheet 1 and 2 which would be your column 'A' on sheet 1 to column 'A' on sheet 2 to see if any of the number match and then copy in the relavant name, irrelevant of whrere they appear in the column.

Sheet1
A B
1 123 bill
2 345 bob
3 678 ben
4 901

Sheet2
A B
1 901
2 123 bill
3 678 ben
4 345 bob


Sorry for the confusion.

 

[KenWright]

Zircom - no confusion - that is exactly what VLOOKUP does. Read the FAQ and you will see examples that look like your data.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[zircom]

excelent help. FAQ very good.

To add to my question:

If i wanted to look for a number and match it, which is working now thank you, can it return a value associated with every occurence of that numer and return it to a single entry? eg:

Sheet 1
1 2 3
A Job No Name Test
B 65482 Name 1 120 (should be 131)
C 52432 Name 2 8

Sheet 2
1 2 3
A Job No Name Test
B 65482 Name 1 120.0
C 52432 Name 2 8.0
D 64582 Name 3 11.0

Thanks

 

[xlbo]

nope - VLOOKUP will only return th 1st unique match
Have a look in the help file at SUMIF instead

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

And if you need to search on more than one variable you can use SUMPRODUCT as opposed to SUMIF.

Some examples here

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------