Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default Advanced Excel: offset, index/match, lookup, other? help!!

I dug deep in my bag of tricks but I cant seem to figure out a solution to a
perplexing problem that I face. I have two data sets; one consists of
various columns of data which includes a project ID and an application ID and
the other consists of various columns of data and an application ID, which in
some instances, matches the application ID in the first data set. I need to
do two things. First, I have to find all the matching records in the two
columns, which Ive done with the following function
(=IF(COUNTIF($BH$51:$BH$1700,$BI$51:$BI$1700)0,A5 1,"")). Second, somehow I
have to get the records that match, to be displayed in the first data set.
For instance, if I look for project ID 95279 and it is linked to application
ID 19466 in the first data set, I want to pull up the corresponding records
that match application ID 19466 in the second data set. Does anyone out
there have any ideas of how this might be accomplished? Ive been a pretty
heavy user of Excel for about four years but I just cant figure this one out€¦

Thanks so much!!

Ryan---



--
RyGuy
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default Advanced Excel: offset, index/match, lookup, other? help!!

Depending on the complexity and volume of your conditions, you can use
SUMPRODUCT or the "Database and List Management functions" (DCOUNT, DGET,
etc.)

--
Regards,
Luc.

"Festina Lente"


"ryguy7272" wrote:

I dug deep in my bag of tricks but I cant seem to figure out a solution to a
perplexing problem that I face. I have two data sets; one consists of
various columns of data which includes a project ID and an application ID and
the other consists of various columns of data and an application ID, which in
some instances, matches the application ID in the first data set. I need to
do two things. First, I have to find all the matching records in the two
columns, which Ive done with the following function
(=IF(COUNTIF($BH$51:$BH$1700,$BI$51:$BI$1700)0,A5 1,"")). Second, somehow I
have to get the records that match, to be displayed in the first data set.
For instance, if I look for project ID 95279 and it is linked to application
ID 19466 in the first data set, I want to pull up the corresponding records
that match application ID 19466 in the second data set. Does anyone out
there have any ideas of how this might be accomplished? Ive been a pretty
heavy user of Excel for about four years but I just cant figure this one out€¦

Thanks so much!!

Ryan---



--
RyGuy

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Advanced Excel: offset, index/match, lookup, other? help!!

Some thoughts for this part ..
For instance, if I look for project ID 95279 and it is linked to application
ID 19466 in the first data set, I want to pull up the corresponding records
that match application ID 19466 in the second data set. Does anyone out
there have any ideas of how this might be accomplished?


Assuming the 2nd data set is in sheet: Y
where col A = app IDs, with cols B to D containing other data

In the 1st data set's sheet,
if col B = app IDs, from B2 down
then we could place in C2:
=IF(ISNA(MATCH($B2,Y!$A:$A,0)),"",INDEX(Y!B:B,MATC H($B2,Y!$A:$A,0)))
then copy C2 to E2 and fill down to retrieve the data corresponding to the
app ID from the 2nd data set
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ryguy7272" wrote:
I dug deep in my bag of tricks but I cant seem to figure out a solution to a
perplexing problem that I face. I have two data sets; one consists of
various columns of data which includes a project ID and an application ID and
the other consists of various columns of data and an application ID, which in
some instances, matches the application ID in the first data set. I need to
do two things. First, I have to find all the matching records in the two
columns, which Ive done with the following function
(=IF(COUNTIF($BH$51:$BH$1700,$BI$51:$BI$1700)0,A5 1,"")). Second, somehow I
have to get the records that match, to be displayed in the first data set.
For instance, if I look for project ID 95279 and it is linked to application
ID 19466 in the first data set, I want to pull up the corresponding records
that match application ID 19466 in the second data set. Does anyone out
there have any ideas of how this might be accomplished? Ive been a pretty
heavy user of Excel for about four years but I just cant figure this one out€¦

Thanks so much!!

Ryan---



--
RyGuy

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
Running Excel 2000 VBA Application on Excel 2003 Excel Worksheet Functions 0 August 8th 06 06:04 PM
Using Excel 2000 VBA Application on Excel 2003 Excel Worksheet Functions 0 August 8th 06 02:36 AM
Where do you learn advanced excel? CompPro Excel Discussion (Misc queries) 4 August 5th 06 03:13 AM
returning offset row from excel table destrolennox Excel Discussion (Misc queries) 4 July 18th 06 10:58 PM
I cant get Excel OFFSET Fnct to return multiple references. dmkirk Charts and Charting in Excel 2 October 15th 05 01:58 PM


All times are GMT +1. The time now is 08:41 PM.

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"