LOOKUP
 

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
.....???
--
Thanks and have a good day

Ruth

LOOKUP
 

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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth

LOOKUP
 

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.

"RWilliams" wrote:

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
....???
--
Thanks and have a good day

Ruth

LOOKUP
 

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" wrote:

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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth

LOOKUP
 

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?
--
Thanks and have a good day

Ruth


"bj" wrote:

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.

"RWilliams" wrote:

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
....???
--
Thanks and have a good day

Ruth

LOOKUP
 

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

On Apr 30, 3:16 pm, RWilliams
wrote:
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" wrote:
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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -

LOOKUP
 

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))

"RWilliams" wrote:

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?
--
Thanks and have a good day

Ruth


"bj" wrote:

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.

"RWilliams" wrote:

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
....???
--
Thanks and have a good day

Ruth

LOOKUP
 

#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?
--
Thanks and have a good day

Ruth


"Pete_UK" wrote:

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

On Apr 30, 3:16 pm, RWilliams
wrote:
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" wrote:
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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -

LOOKUP
 

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

On May 1, 10:01 am, RWilliams
wrote:
#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?
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
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

On Apr 30, 3:16 pm, RWilliams
wrote:
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" wrote:
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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

LOOKUP
 

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,VLOOK UP($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 ..
--
Thanks and have a good day

Ruth


"Pete_UK" wrote:

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

On May 1, 10:01 am, RWilliams
wrote:
#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?
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
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

On Apr 30, 3:16 pm, RWilliams
wrote:
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" wrote:
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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

LOOKUP
 

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

On May 1, 1:44 pm, RWilliams
wrote:
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,VLOOK UP($A$38&$B$38,$C$26:$G$3*4,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 ..
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
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

On May 1, 10:01 am, RWilliams
wrote:
#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?
--
Thanks and have a good day

Ruth

"Pete_UK" wrote:
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

On Apr 30, 3:16 pm, RWilliams
wrote:
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" wrote:
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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

LOOKUP
 

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
--
Thanks and have a good day

Ruth


"Pete_UK" wrote:

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

On May 1, 1:44 pm, RWilliams
wrote:
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,VLOOK UP($A$38&$B$38,$C$26:$G$3Â*4,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 ..
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
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

On May 1, 10:01 am, RWilliams
wrote:
#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,FALS E))
because this returns the standard Error message and highlights the "" as the
first error

Any thoughts on what i am doing wrong?
--
Thanks and have a good day

Ruth

"Pete_UK" wrote:
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

On Apr 30, 3:16 pm, RWilliams
wrote:
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" wrote:
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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

LOOKUP
 

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

On May 3, 1:08 pm, RWilliams
wrote:
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
--
Thanks and have a good day

Ruth



"Pete_UK" wrote:
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

On May 1, 1:44 pm, RWilliams
wrote:
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,VLOOK UP($A$38&$B$38,$C$26:$G$3**4,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 ..
--
Thanks and have a good day

Ruth

"Pete_UK" wrote:
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

On May 1, 10:01 am, RWilliams
wrote:
#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?
--
Thanks and have a good day

Ruth

"Pete_UK" wrote:
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

On Apr 30, 3:16 pm, RWilliams
wrote:
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" wrote:
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

On Apr 27, 11:30 am, RWilliams
wrote:
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
....???
--
Thanks and have a good day

Ruth- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -