Excel search query

  vsh1 11:48 08 Oct 07
Locked

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 07

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 07

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 07

Does this help?

click here

  vsh1 11:40 09 Oct 07

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

  vsh1 11:54 09 Oct 07

BINGO! Many thanks

  silverous 12:38 09 Oct 07

Great, can you tick as resolved? Thanks.

  vsh1 12:59 09 Oct 07

My pleasure

  vsh1 14:46 09 Oct 07

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 07

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 07

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.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…