MATCH, INDEX, LOOKUP - Help!

[RobPot]

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

[mbasi]

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

[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



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

[RobPot]

So apart from the thread hijack and solution above (!) - anyone got any 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: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 Data>Filter>Autofilter 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!)
>
>