Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 :| |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large Index Match Lookup | Excel Worksheet Functions | |||
lookup, index, match, offset, etc. | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |