Software

Ask something
Discussion Locked

Searching in MS Excel 2000

0 helpful
5 replies

by d'arcy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to figure out a way to search through multiple columns and rows for a value, then either return the row number it was located in, or the value of the first column in that row.

Something like this (hopefully formatting stays the same.
A B C D E F G H
-----------------------------------------------
1|IT | 1 | 8 | 6 | | | | |
-----------------------------------------------
2|HR | 2 | 9 | 4 | 11 | | | |
-----------------------------------------------
3|ACC | 21 | 98 | 42 | | | | |
-----------------------------------------------
4|MGM | 51 | 99 | 10 | 7 | | | |
-----------------------------------------------
5|TMP | 17| 22 | | | | | |
-----------------------------------------------

That's the data sample, now off to the side, I want to have the values that populate the table (B1:H5) listed, and beside them, the heading for the row in whichthey are located(A1:A5). Each number in the table is unique, but may be moved from row to row, and each row isn't sorted (can be sorted if necessary). The result would be something like:

10 MGM
4 HR

Now, if 4 was moved a row up, itwould change to:

10 MGM
4 IT

I've been racking my brains for a while now, any help will be much appreciated.

Thanks...

5 replies

by d'arcy

Sorry, font changed, if you can picture it, it's supposed to be a table with some numbers in it and some blank cells, all the | | |... are blank cells...

Thanks

by dklippert

There maybe a more elegant solution.
I created a list of the numbers in the table. They are in a column from G1 to G16.
I then entered the formula:

=INDIRECT("A" & MIN(IF($B$1:$H$5=G1,ROW(1:5))))

This has to be Array entered Ctrl+Shift+Enter.
When you do this, you'll see brackets aroud the formula
{=INDIRECT("A" & MIN(IF($B$1:$H$5=G1,ROW(1:5))))}.
These cannot be entered by "hand"

The problem I had was trying to fill down the formula. I had to go back and edit the "1:5" so that it didn't increment.
Let me know if this helps.

by dklippert

(type in haste, correct at leisure)
I actually put the list in J and used absolutes for the rows.
Here is an array entered formula:

{=IF(ISERROR(INDIRECT("A" & MIN(IF($B$1:$H$5=J3,ROW($2:$6))))),"Null",INDIRECT("A" & MIN(IF($B$1:$H$5=J3,ROW($2:$6)))))}

To see a sample spreadsheet go to:

www.Klippert.com/Excel

Choose "Cell Address in an array" from the Topic list.

This discussion is closed to new replies.