data extraction

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello again

Another problem, relating to the same sheet as discussed previously.

On sheet 1, Row 1, 21, 41, 61 etc has plot numbers from 1 - 20, 21 - 40, 41 - 60 etc in columns C - V. ie, Cell C1 = 1, Cell V21 = 40 etc

Now the Cell under these plot numbers is a different house type, ie, Ascot, Windsor, Kempton etc. Therefore Cell C2 may = Asc, Cell V21 may = Wind.

The problem is this. On sheet 2 I would like to be able to enter the plot number in Cell A24 and I would like the house type to appear automatically in Cell B24 without having to return to sheet 1 and look up the house type.

Is this possible?
 
Store the plot numbers and house types in a vertical list on sheet 3,
and you can use a VLookup formula to extract the house type.

If you need to print the list in the current format, use Vlookups on
sheet 1, to return the house type from the vertical list.
 
Hi Lee,

Here's one way...

Sheet2!B24:

=INDEX(Sheet1!C1:V42,MATCH(Sheet2!A24,Sheet1!C1:C42,1)+1,MATCH(A24,OFFSET(Sheet1!C1:V1,MATCH(Sheet2!A24,Sheet1!C1:C41,1)-1,0),1))

I've only taken into account up to Row 42, so adjust the rang
accordingly.

Hope this helps
 
Thanks Debra, works great.


Debra Dalgleish said:
Store the plot numbers and house types in a vertical list on sheet 3,
and you can use a VLookup formula to extract the house type.

If you need to print the list in the current format, use Vlookups on
sheet 1, to return the house type from the vertical list.
Click to expand...
 
Back
Top