Excel search query

  vsh1 11:48 08 Oct 2007

Can I write a query that searches in a table against specific fields in the rows and columns, so that it gives the value of the field where they cross each other?

  silverous 13:41 08 Oct 2007

Do you mean a matrix ?
So if for example we had a spreadsheet recording levels of stock of a particular product in a particular country, with products listed down the rows and countries across the top, you want to say lookup the stock level of a particular product in a particular country ??

  vsh1 08:11 09 Oct 2007

Yes I think so! Look down column A and accross to row 1 and get the value in that cell. I have a huge table and want to input the value of column A in one cell, the value of row 1 in another and have a formula display the resultant 'search' in a third; if that all makes sense?

  silverous 11:24 09 Oct 2007

Does this help?

click here

  vsh1 11:40 09 Oct 2007

Think so! I'll have a bash, thanks.

  vsh1 11:54 09 Oct 2007

BINGO! Many thanks

  silverous 12:38 09 Oct 2007

Great, can you tick as resolved? Thanks.

  vsh1 12:59 09 Oct 2007

My pleasure

  vsh1 14:46 09 Oct 2007

Another issue has now come up. The formula works great but if I copy it to cells below, although it incrementally goes down the column for the first cell in parenthesis (the row) and the column in the second, it also increases the range of cells by one. So for
=VLOOKUP(B5,'Table 1 reading age'!A5:H26,MATCH(C5,'Table 1 reading age'!A4:H4),FALSE)
if I copy it to the next cell down it becomes
=VLOOKUP(C5, 'Table 1 reading age'!A6:H27,MATCH(D5,'Table 1 reading age'A5:H27),FALSE)
How do I ensure only the single cell reference increases incrementally?

  BigAl127 15:55 09 Oct 2007

Use an absolute cell Reference, whereby the cell you need to not increase, insert the $ sign as per example below:-

=VLOOKUP($B$5,'Table 1 reading age'!A5:H26,MATCH(C5,'Table 1 reading age'!A4:H4),FALSE)

B5 would remain the same all the way down.

  silverous 16:25 09 Oct 2007

Or do you mean the lookup table? That should presumably be fixed also otherwise the lookup table is moving as you copy down?

If so you do similar i.e. put $ in front of whichever aspects need fixing e.g.:

=VLOOKUP($B$5,'Table 1 reading age'!$A$5:$H$26,MATCH(C5,'Table 1 reading age'!A$4:H$4),FALSE)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?