Advanced Excel: offset, index/match, lookup, other? help!!

R

ryguy7272

I dug deep in my bag of tricks but I can’t 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 I’ve done with the following function
(=IF(COUNTIF($BH$51:$BH$1700,$BI$51:$BI$1700)>0,A51,"")). 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? I’ve been a pretty
heavy user of Excel for about four years but I just can’t figure this one out…

Thanks so much!!

Ryan---

[email protected]
 
P

PapaDos

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

Max

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?
Click to expand...

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,MATCH($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
 
Top