hlookup query

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have a column with different reference numbers for the
lookup appearing each 20 rows or so. How can I get lookup
to find the numbers (such as 2 below, so it can then refer
to the specific numbers below each reference number) given
it's normal function only looks for the number in the top
row?

eg
1
485
846
859
948
2
945
404
847
847
3
etc
Thanks
 
Bruce

One way:

Assuming your data in A3:A100, the number to lookup
in C3 enter this formula in C4:

=OFFSET($A$3,MATCH($C$3,$A$3:$A$100,0)+ROW()-ROW($C$4),0)

Drag C4 down with the fill handle (the little square in the lower right
corner of
the cell) as long as necessary.

ROW($C$4) is used as a counter, so if you start the formula in e.g. G12,
ROW($C$4) must be replaced by ROW($G$12)

Enter a number (1, 2, 3 ....) in C3 and below you will have the matching
sequence of numbers.


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Leo,
Thanks for the response but I don't think it'll do what
I'm looking for. I want it so that if I enter 2 in the
lookup cell, the formulas beneath the lookup cell will
return:
945
404
847
and if I enter 1 those below will return:
485
846
859
948
I can make hlookup work but it will only look for the 1
(being in the top row), I want it to look for any
reference number from the column (1, 2, 3 etc) and return
the relevant set of numbers underneath! I hope you can
help aagain, thanks

Bruce
 
Bruce

Your original posting didn't mention anything
about duplicates not being displayed (if
that's, what your answer is about?)

Before I revise my formula, please disclose,
if there always is the same amount of numbers
between two lookup values: between 1 and 2,
between 2 and 3, etc. (4 in the original posting)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Leo,
There will be anywhere up to 40 numbers between each
reference number (eg between 1 and 2 or 2 and 3 or 3 and
4) The numbers will differ but as each lookup value is
entered (one at a time of course) I need the result to be
the corresponding >40 numbers below.

Thanks Bruce
 
Bruce

In that case this formula should do the trick:
In C4 enter:

=IF(OR(ROW()-ROW($C$4)+1>MATCH($C$3+1,$A$3:$A$100,0)-
MATCH($C$3,$A$3:$A$100,0)-1,COUNTIF($C$3:C3,OFFSET($A$3,
MATCH($C$3,$A$3:$A$100,0)+ROW()-ROW($C$4),0))>0),"",
OFFSET($A$3,MATCH($C$3,$A$3:$A$100,0)+ROW()-ROW($C$4)

Copy C4 down 40 cells with the fill handle.

To get the list for the last lookup number, the list must be
terminated with the next lookup number, e.g. if 5 is the last
lookup number:

5
345
2356
5432
345
6

and of course the lookup numbers must be unique in the list!


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Back
Top