lookup problems

  • Thread starter Thread starter Dig-IT
  • Start date Start date
D

Dig-IT

Hi,
I'm seriously stumped on the following problem:

I have a table with rows (knowledge disciplines e.g. "Excel",
"Word",etc) and columns (names e.g. John, Mary, Hank). The entries in
the table denote the knowledge level in the discipline:

John Mary Hank
Excel much little much
Word little much little

I'm trying to create an interface in which the user can select a
discipline from a listbox. Excel should return the names corresponding
to the values "much" in the table, but the numer of names is unknown.

If the users' choice for "Excel" or "Word" is in cell K365, then
"=MATCH(K365;A1:A300;0)" gives me the row to look at. Alternatively,
"=VLOOKUP(K365;A1:Z300;COLUMN(B:D);0)" gives me an array with all results.

That's as far as I've got today, unfortunately. Can anyone help me
further? I have to do this in Excel and I know very little of it. (How I
long for Matlab at the moment... ;-)

By the way, the real table will be about 50x50 in size.

Thanks in advance,
Jos
 
I would do this with a Countif-formula, in combination with a ListBo
(Forms toolbar). The countif-range can be referenced by the linked cel
of the List Box.

Hm, on re-reading your question, I guess my answer isn't exactly wha
you want. You will probably need a macro
 
http://www.cpearson.com/excel/lookups.htm#DoubleLookup

Take a look at this... it may help you---
Click to expand...

The web page seems to be offline, but I've found mirrors using Google.
It's not exactly what I need. My problem is that I know which row to
look in, but not which columns I need.

What I need is something like:
- For a given (dynamic) row number (e.g. 3)
- Look at all the occurrences of the string "much" (e.g. B3, E3 and G3)
- For each occurrence, report the value in that column, first row (e.g.
B1, E1 and G1)
- the report can be multiple cells or a concatenated string, I don't
care. I just don't know the number of occurrences in advance.

I cannot program in VBA, so I hoped there would be a worksheet solution
to this. Any additional help as still greatly appreciated!

Cheers,
Jos
 
Back
Top