 Excel question |
  |
| Excel question |
 Jun 14 2007, 11:38 AM
Post
#1
|
|
|
Group: Admin Posts: 6,906 Joined: 22-February 06 From: Austin Member No.: 9  |
I'm looking for something similar to the MATCH command in Excel, specifically a command (or combination of commands) that will search for a value and return a range of cells that the value appears in, rather than just a single cell location like MATCH does. Any ideas?
-------------------- |
 |
 
|
|
Jun 14 2007, 12:05 PM
Post
#2
|
|
|
Fool Group: Members Posts: 2,127 Joined: 23-February 06 From: LBB Member No.: 56 |
I don't see a "Lookup and Reference Function" that does what you described. Only thing I can think of is running a loop over your data set and flagging each cell that matches for output.
-------------------- Spam? Isn't that something poor people eat?
|
|
|
|
|
Jun 14 2007, 12:23 PM
Post
#3
|
|
 Group: Members Posts: 5,275 Joined: 22-February 06 Member No.: 2 |
=COUNTIF('my_tab'!D:D,"asdf") will count how many times "asdf" appears in the D column. I know you want a range of cells that contain "asdf", but this might be helpful if you could use the total number of asdf's in some sort of match loop.
|
|
|
|
|
Jun 14 2007, 12:36 PM
Post
#4
|
|
|
Fool Group: Members Posts: 2,127 Joined: 23-February 06 From: LBB Member No.: 56 |
Oh yeah, how's your data structured? Is it completely random or is it in some kind of order? Like will the cells that are supposed to match always be connected?
If in ascending order, then you could find your lower bound by using MATCH(<number>,<lookup array>,0) and upper bound by using MATCH(<number+1>,<lookup array>,0). The lower bound would be contained in and the upperbound would take the cell just before it. -------------------- Spam? Isn't that something poor people eat?
|
|
|
|
|
Jun 14 2007, 12:58 PM
Post
#5
|
|
|
Group: Admin Posts: 6,906 Joined: 22-February 06 From: Austin Member No.: 9 |
Ok, I see how to do this. I can use MATCH to find the lower bound, then that cell+COUNTIF-1 to find the upper bound. The data is structured in ascending order, but I'm searching for a text value, not numeric, so MATCH(<number+1>,<lookup array>,0) doesn't work.
-------------------- |
|
|
|
|
| Lo-Fi Version | Time is now: 18th August 2026 - 03:17 PM |
