LOOKUP

R

RWilliams

I have a large amount of data out of Project and i want to search for a row
that satisfies 2 specified criteria from column A and B and copy and paste
the information for that row (columns A to X) into a particular position on
another sheet ... I need to do this for approximatley 100 sets of criteria so
do not want to do it manually if i can help it .... I am not even sure where
to start with this ... if anyone has any thoughts I would be happy to try
.....???
 
P

Pete_UK

You will need to supply a bit more information if you want a specific
solution, but by concatenating A and B together (in a newly inserted
column C on the sheet you have) then you could make use of VLOOKUP. It
might be better to set this up on a new sheet, where A2 and B2 contain
the sets of criteria you are interested in, then if your original
sheet is called Sheet1 you would have something like this in C2 of the
new sheet:

=VLOOKUP($A2&$B2,Sheet1!$C$2:$Y$1000,COLUMN()-1,0)

The formula can be copied across to X2 to return the data which
matches. You can also put other criteria in A3/B3 etc down these
columns, and copy the formula down.

Hope this helps.

Pete
 
B

bj

one way would be to enter your pair criteria in columns A abd B in a new
sheet (Sheet2)
In C1 enter
=SUMPRODUCT(--(Sheet1!A$1:A$64000=A1),--(Sheet1!B$1:B$64000=B1)*ROW(A$1:A$64000))
and copy down as far as you need
In D1 enter
=OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4)
copy over to AC1 and down as far as you need.
 
R

RWilliams

Thanks - between your reply and BJ's I now have my resolution - is there any
way i can use Conditional Formatting to hide the 'not applicable' responses?
--
Thanks and have a good day

Ruth


Pete_UK said:
You will need to supply a bit more information if you want a specific
solution, but by concatenating A and B together (in a newly inserted
column C on the sheet you have) then you could make use of VLOOKUP. It
might be better to set this up on a new sheet, where A2 and B2 contain
the sets of criteria you are interested in, then if your original
sheet is called Sheet1 you would have something like this in C2 of the
new sheet:

=VLOOKUP($A2&$B2,Sheet1!$C$2:$Y$1000,COLUMN()-1,0)

The formula can be copied across to X2 to return the data which
matches. You can also put other criteria in A3/B3 etc down these
columns, and copy the formula down.

Hope this helps.

Pete
Click to expand...
 
R

RWilliams

Thanks - between your reply and Pete's I now have my resolution - is there
any way i can use Conditional Formatting to hide the 'not applicable'
responses?
 
P

Pete_UK

Do you mean that you get #N/A errors showing up where there is no
match? If so, then you can trap this by modifying the formula along
the lines:

=IF(ISNA(VLOOKUP( ... ),"",VLOOKUP( ... ))

Basically, this means if there is an error then return "" (blank
cell), otherwise do the Vlookup. You could make "" into something more
meaningful if you wish, like "not present".

Hope this helps.

Pete
 
B

bj

where does the "Not applicable" come in. Do you want to hide the data on
Sheet1?
or is "not applicable" one of the possible responses in sheet 2

you could use conditional format with text color white if equals "Non
applicable or you could change the formula to
=if(OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4)="Non
applicable","",OFFSET(Sheet1!$A$1,$C1-1,COLUMN()-4))
 
R

RWilliams

#N/A is exactly what i want to avoid .... your suggestion is great but does
not seem to work with my formula =VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE)

Should your version look like:
=IF(IsNA(VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE),"",(VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE))
because this returns the standard Error message and highlights the "" as the
first error

Any thoughts on what i am doing wrong?
 
P

Pete_UK

Hi Ruth,

Sorry, I missed an internal close-bracket on my last post. Your
formula should be:

=IF(ISNA(VLOOKUP($A$38&$B$38,$C$26:$G$34,2,FALSE)),"",VLOOKUP($A$38&$B
$38,$C$26:$G$34,2,FALSE))

All one formula - be wary of line breaks.

Hope this helps.

Pete
 
R

RWilliams

Hi Pete

Thanks - I should have noticed something as simple as that ... it works now
... :)

I had also come up with
=IF(ISNA(MATCH($A$38&$B$38,$C$26:$C$34,0)),0,VLOOKUP($A$38&$B$38,$C$26:$G$34,3,FALSE))
which also works - would there be any advantage to using one or the other
formula?

I promise to stop asking questions and it has been great to get some quick
answers ..
 
P

Pete_UK

MATCH works a bit quicker than VLOOKUP, so if you have a lot of these
formulae copied down on the sheet or if you have a large lookup table,
then your formula may show some speed improvements. As you are using a
lot of absolute cell references, though, I suspect you only have the
formula in one cell, so there will be no noticeable difference in
timing.

Thanks for feeding back - I'm glad it worked for you.

Pete
 
R

RWilliams

Thanks - I will actually be using this formula in a couple of thousand cells
.... with slight variations - extracting day to day data entries from
information held in MS Project and comparing it to other data held on an xls
- a resource supply and demand exercise .. so the faster it works the better
... So thanks for the help
 
P

Pete_UK

Hi Ruth,

Check out this site for more considerations on optimising speed:

http://www.decisionmodels.com/optspeede.htm

If you intend to copy the formula across columns, then you can speed
things up by not having the

=IF(ISNA(MATCH( ... )),0,VLOOKUP( ... ))

in every cell. As you have already checked for a match with this
formula in column C, then in column D you could have:

=IF($C38=0,0,VLOOKUP( ... ))

and copy this across. Then copy this row down.

Hope this helps.

Pete
 
Top