[Solved] Searching by rows & columns

[bickle]

hopefully as normal there is a straight forward answer but my brain is frazzled today and i can't get my head round it.

i have attached a condensed version of what i am trying to do, the row info and and the data will be pulled in from elsewhere i just need to interrogate the table.

hopefully it is clear any questions let me know.

brgds

[jrkrideau]

hopefully as normal there is a straight forward answer but my brain is frazzled today and i can't get my head round it.

i have attached a condensed version of what i am trying to do, the row info and and the data will be pulled in from elsewhere i just need to interrogate the table.

hopefully it is clear any questions let me know.

brgds

I'm not very good with calc so I don't see any easy way to do this. Do you have to have the data in this type of table? It is not ideal for searching. If you could have it in a layout like this (not all the data in included)"

Code: Select all

names	dates	values
balhrphx	01/01/08	655
balhrmex	01/01/08	729
balhrsyd	01/01/08	745
balgwjer	01/01/08	111
balgwgnd	01/01/08	528
balhrphx	02/01/08	655
balhrmex	02/01/08	729
balhrsyd	02/01/08	745
balgwjer	02/01/08	111
balgwgnd	02/01/08	528
balhrphx	03/01/08	655
balhrmex	03/01/08	729
balhrsyd	03/01/08	745
balgwjer	03/01/08	111
balgwgnd	03/01/08	528
balhrphx	04/01/08	549
balhrmex	04/01/08	646
balhrsyd	04/01/08	762
balgwjer	04/01/08	128
balgwgnd	04/01/08	545

then you can use a standard filter to do the work. Data > Filter > Standard Filter

[bickle]

thks but having all the data listed that way and flitering doesn't do the job i'm after as there is a lot of additional rows to add

i had envisaged a vlookup on column A followed by an hlookup along row 1 to give the value corresponding to the vlookup and hlookup results. i haven't been able to combine these, and am not sure if it can been done this way anyway.

(had also looked at the DGET function which i thought might work but again failed miserably !)

any other suggestions welcomed

best regards

[squenson]

A combination of the MATCH() and INDEX() functions should do the trick. MATCH(value; range; 0) return the position of value in the range (0 means that the range is unsorted). INDEX(range; r; c) returns the value of the cell of the range at the intersection of the rth row and cth column. Therefore, in your case, try:

Code: Select all

=INDEX(B2:K6;MATCH(A11;A2:A6;0);MATCH(B11;B1:K1;0))

[bickle]

thks works perfectly, exactly what i was after.

best regards