MATCH, INDEX, LOOKUP - Help!

[RobPot]

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

Any ideas?
Many thanks

Rober

 

[mbasi]

Hey everyone,

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

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

A | B |
|
____________________________________________________
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 stay
as B1. When I enter the formula in the first cell (C4) and then drag t
the end of the column, the section in the formula where it shows *B
changes to whatever row I am in.

For example:

A | B |
|
____________________________________________________
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 th
formula.

I hope this makes sense.

Many thanks

Mani :

 

[Roger Govier]

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

 

[RobPot]

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

 

[Roger Govier]

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,MATCH('Crime report'!D,'CSI report'!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,"="&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 Data>Filter>Autofilter use the dropdown on the
column with your formulae to select the 1's


Regards

Roger Govier