Football - finding a corporate box holders seat in the stadium

[Dave]

Hi,

Basically I am trying to compare 2 s/sheets using vlookup

sheet 1 contains 240 records of customer and seat numbers of people
sitting in a corporate box at a football stadium.

sheet 2 contains 30,0000 records of all customers sitting in the actual
stadium seats (including the above box holders who have a normal seat
for the game).

What I'm trying to obtain is 1 report with the box seat details and
their corresponding stadium seat numbers.

At the moment I can use vlookup to check the customer number in sheet 1
and find it in sheet 2. The problem arises where a single customer has
purchased 10 seats, and vlookup returns the same seat number for all 10
stadium seats i.e. if they have seats 1-10 it will just return seat 1
for all 10 records wheras I want it to return 1 for the 1st record,
then 2 for the 2nd etc.

I guess what I'm asking for is vlookup to index and start it's search
where it left off from the previous search.
Any help would be greatly appreciated.

 

[Bernie Deitrick]

Dave,

A technique that could help would be to use a formula in another column that
returns True or False, and use that column to filter your entire data set.

For example, if your names are in column A of sheet2, and you want to
extract all the values on Sheet2 column B, whose entry in column A matches
that of cell A3 of the current sheet, then you could array enter this
formula (using Ctrl-Shift-Enter) into cell B3:

=INDIRECT("'Sheet2'!B"
&LARGE((Sheet2!$A$1:$A$100=Sheet1!$A$3)*ROW(Sheet2!$A$1:$A$100),ROW()-ROW($A
$3)+1))

Copy down as far as you need (ten rows if the most you expect to get back is
10 values, for example).

This formula as written will return errors for the extra cells beyond those
matched, but you could use an simple

=IF(ISERROR(....formula above....),"",....formula above...)

to suppress the display of those errors.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Dave,

I should have preceeded the second paragraph with an "Also....." since it
is unrelated to the filtering technique. Sorry for being so braindead - I
answered a call in the middle of writing this, and got sidetracked.

HTH,
Bernie
MS Excel MVP