Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Question Comparing 2 columns from 2 different Worksheet and Returing Value of a 3rd column

I have 2 different worksheets, I need to compare Column D of worksheet 1 to the range of column A of worksheet 2. If the value in Column D worksheet 1 will match the value in the range of column A worksheet 2 I need to get the value in Column O of worksheet 2.

e.g.
Compare value of worksheet 1 column D row 2 to worksheet 2 column A rows 2 - 1000. If worksheet 1 D2 appears anywhere in worksheet 2 A1 to A1000 I want to return the text/value in worksheet 2 column O.

If worksheet 1 D2 = worksheet 2 A347 I want to return the text/value in worksheet 2 O347. The repeat this from D2 to D500
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 153
Default Comparing 2 columns from 2 different Worksheet and Returing Valueof a 3rd column

I have 2 different worksheets, I need to compare Column D of worksheet 1
to the range of column A of worksheet 2.

Compare value of worksheet 1 column D row 2 to worksheet 2 column A rows
2 - 1000. If worksheet 1 D2 appears anywhere in worksheet 2 A1 to A1000
I want to return the text/value in worksheet 2 column O.

If worksheet 1 D2 = worksheet 2 A347 I want to return the text/value in
worksheet 2 O347. The repeat this from D2 to D500


One way is to put this in Sheet1!B2 and copy down as far as needed:
=IF(ISERROR(1/(VLOOKUP(D2,Sheet2!A:O,15,FALSE)<"")),"",
VLOOKUP(D2,Sheet2!A:O,15,FALSE))
The ISERROR(...) part is meant to return an empty string in two cases:
- The value searched for is absent from Sheet2 column A.
- It's present, but the corresponding "column O" cell is empty.
  #3   Report Post  
Member
 
Location: Birmingham
Posts: 35
Default

Quote:
Originally Posted by sourcing View Post
I have 2 different worksheets, I need to compare Column D of worksheet 1 to the range of column A of worksheet 2. If the value in Column D worksheet 1 will match the value in the range of column A worksheet 2 I need to get the value in Column O of worksheet 2.

e.g.
Compare value of worksheet 1 column D row 2 to worksheet 2 column A rows 2 - 1000. If worksheet 1 D2 appears anywhere in worksheet 2 A1 to A1000 I want to return the text/value in worksheet 2 column O.

If worksheet 1 D2 = worksheet 2 A347 I want to return the text/value in worksheet 2 O347. The repeat this from D2 to D500
If you move Column O to Column B in worksheet 2 then you could use a VLOOKUP to do this...

=VLOOKUP('Worksheet1'!D2,'Worksheet2'!$A$1:$B$1000 ,2,FALSE)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching Multiples Words in cells and returing it in adjacent cell azhar.abidi New Users to Excel 0 January 16th 12 07:37 PM
Comparing two columns some same some add up to others in column 2 navelkist Excel Discussion (Misc queries) 1 March 4th 09 01:22 AM
Comparing two columns in two different worksheet. Waleed Mahmoud Excel Programming 1 November 5th 07 07:15 AM
comparing 2 columns info to fill 3rd column quinn111 Excel Worksheet Functions 6 October 5th 07 01:25 AM
Formula that checks several criteria before returing the value??? Apinun Excel Discussion (Misc queries) 4 September 19th 06 10:17 AM


All times are GMT +1. The time now is 09:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"