Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 8th 08, 02:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1
Default Need to match 2 columns, if a match found add info from 2nd column

I'm comparing 2 lists of serial numbers. The first list is smaller. The 2nd
list is more extensive and contains information in 3 more columns that needs
to be added to the 1st list.

Example:

12345678 X 12345677
12345678 Mr Joe Bloggs Feb 08 £1500
12345679 X 12345679 Mr Fred Smith Apr 08 £600
12345681 X 12345680
12345682 X 12345681 Mrs Jane Doe Apr 08 £750

If the number exists in both columns, I need the remaining info (name, date,
amount etc) to be inserted at X.

Please can someone help?

  #2   Report Post  
Old October 8th 08, 02:55 PM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Need to match 2 columns, if a match found add info from 2nd column

A simple index/match would be one way

Assuming from your posted data, the 1st list is in col A,
the 2nd is in cols C to F, with data from row1 down

First, insert 3 new cols in-between the 2 lists (for the data to be
extracted)
so that you have the 1st list in col A, with the 2nd list in cols E to H

Then put in B1:
=IF(ISNA(MATCH($A1,$E:$E,0)),"",INDEX(F:F,MATCH($A 1,$E:$E,0)))
Copy B1 to D1, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"Stratton" wrote:
I'm comparing 2 lists of serial numbers. The first list is smaller. The 2nd
list is more extensive and contains information in 3 more columns that needs
to be added to the 1st list.

Example:

12345678 X 12345677
12345678 Mr Joe Bloggs Feb 08 £1500
12345679 X 12345679 Mr Fred Smith Apr 08 £600
12345681 X 12345680
12345682 X 12345681 Mrs Jane Doe Apr 08 £750

If the number exists in both columns, I need the remaining info (name, date,
amount etc) to be inserted at X.

Please can someone help?



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
Match using array of column and row references to match with jkfin1 Excel Worksheet Functions 1 September 16th 08 04:39 PM
Any way for 2 column vlookups. i.e match last name then match firs CraigS Excel Worksheet Functions 5 March 7th 06 01:30 AM
can lookup return err if no match found Kim Greenlaw Excel Worksheet Functions 12 January 12th 06 05:27 PM
Find text in another workbook and paste if found match - VBA Pasmatos Excel Discussion (Misc queries) 1 November 10th 05 02:00 PM


All times are GMT +1. The time now is 09:53 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017