MATCH, INDEX, LOOKUP - Help!
 

Hi all,
I have two worksheets 'Crime report' and 'CSI report' where the common
link is the Unique Reference Number. However there are more crime
reports than CSI reports.
What I'm trying to get to is a third worksheet where the URNs are shown
where they have appeared on both previous sheets - I'm guessing that I
can then pull any additional data I need using an OFFSET formula.
I tried:
=MATCH('Crime report'!D:D,'CSI report'!D:D,0)
but that only returns the cell reference rather than the number and I
can't work out what to do next.
Just to be clearer the data is structured with each report across a row
with columns headings such as URN, date, address etc. in the above
formula the URN is in column D on each sheet.

Any ideas?
Many thanks

Robert


--
RobPot
------------------------------------------------------------------------
RobPot's Profile: http://www.excelforum.com/member.php...o&userid=18721
View this thread: http://www.excelforum.com/showthread...hreadid=472924

Hey everyone,

I am doing some calculations on excel and I'm trying to make my life
more simple.

For example, I'm trying to do the followowing:

A | B | C
|
__________________________________________________ __
1|Percentage Increase:|10 |
|
2|
3|Desc: |Price | After Increase
|
4|Base 300 |10.50 |=B4*2.7/100*105*82/100*B1 |
5|Wall 300 | 8.50 |=B5*2.7/100*105*82/100*B1
|
6|Base 600 | 20.00|=B6*2.7/100*105*82/100*B1 |

Basically my queston is, how do I enter the B1 and make sure it stays
as B1. When I enter the formula in the first cell (C4) and then drag to
the end of the column, the section in the formula where it shows *B1
changes to whatever row I am in.

For example:

A | B | C
|
__________________________________________________ __
1|Percentage Increase:|10 |
|
2|
3|Desc: |Price | After Increase
|
4|Base 300 |10.50 |=B4*2.7/100*105*82/100*B1 |
5|Wall 300 | 8.50 |=B5*2.7/100*105*82/100*B2
|
6|Base 600 | 20.00|=B6*2.7/100*105*82/100*B3 |

I want the B1 section to be frozen and remain as it is throughout the
formula.

I hope this makes sense.

Many thanks

Mani :|


--
mbasi
------------------------------------------------------------------------
mbasi's Profile: http://www.excelforum.com/member.php...o&userid=27780
View this thread: http://www.excelforum.com/showthread...hreadid=472924

Hi

I'm not sure how your message got into this thread, but the answer to
your problem is to use $ to fix the cell location
$B$1

The first $ makes the column fixed, the second $ makes the row fixed.

Regards

Roger Govier



mbasi wrote:

Hey everyone,

I am doing some calculations on excel and I'm trying to make my life
more simple.

For example, I'm trying to do the followowing:

A | B | C
|
_________________________________________________ ___
1|Percentage Increase:|10 |
|
2|
3|Desc: |Price | After Increase
|
4|Base 300 |10.50 |=B4*2.7/100*105*82/100*B1 |
5|Wall 300 | 8.50 |=B5*2.7/100*105*82/100*B1
|
6|Base 600 | 20.00|=B6*2.7/100*105*82/100*B1 |

Basically my queston is, how do I enter the B1 and make sure it stays
as B1. When I enter the formula in the first cell (C4) and then drag to
the end of the column, the section in the formula where it shows *B1
changes to whatever row I am in.

For example:

A | B | C
|
_________________________________________________ ___
1|Percentage Increase:|10 |
|
2|
3|Desc: |Price | After Increase
|
4|Base 300 |10.50 |=B4*2.7/100*105*82/100*B1 |
5|Wall 300 | 8.50 |=B5*2.7/100*105*82/100*B2
|
6|Base 600 | 20.00|=B6*2.7/100*105*82/100*B3 |

I want the B1 section to be frozen and remain as it is throughout the
formula.

I hope this makes sense.

Many thanks

Mani :|

MATCH, INDEX, LOOKUP - Help!
 

So apart from the thread hijack and solution above (!) - anyone got any
ideas?
(bump!)


--
RobPot
------------------------------------------------------------------------
RobPot's Profile: http://www.excelforum.com/member.php...o&userid=18721
View this thread: http://www.excelforum.com/showthread...hreadid=472924

MATCH, INDEX, LOOKUP - Help!
 

Hi Robert

The numbers being returned are the row numbers where a match is found. Since
you are dealing with column D, then the cell reference would be Dn where n
is your numeric value.

Wrapping your formula in an INDEX() function will return the URN's

=INDEX('Crime Report'!D:D,MATCH('Crime report'!D:D,'CSI report'!D:D,0))
but this will return #N/A where there is no match.

I'm not sure what you want to do with the data, or whether this helps.

An alternative approach could be to use a spare column on Crime Sheet and enter
=COUNTIF('CSI Report'!D:D,"="&D1)
and copy down the column.
This will return a 1 where there is a match and 0 where there isn't.
Mark your block of data and DataFilterAutofilter use the dropdown on the
column with your formulae to select the 1's


Regards

Roger Govier


RobPot wrote:
So apart from the thread hijack and solution above (!) - anyone got any
ideas?
(bump!)