finding a cell address

[matmich]

I am still working on a vlookup table to return more All values for
given lookup value. I have found a way but need a little help.

my first value is easy it just looks at the range of the whole databas
lookup range= a:d

The second value is tricky, what I need is a formula that will give th
me address of the 1st match+1 cell down
what will give me lookup range = a2:d3

The third would be the value of the 1st match +2 cell down

My data is sorted by col. A in ascending order -- thats why once i
finds the 1st one if there are any more it will be in the next row.

I have an example if needed

a b c d
1 106 aj 3 5
2 106 aj 1 2
3 106 aj 5

 

[Frank Kabel]

Hi
not quite sure about your spreadsheet layout. But have a look at the
MATCH function. It returns a row index. This in combination with INDEx
should be what you're looking for

 

[matmich]

frank here are my formulas- my lookup value is in H4,
remember my database is
sorted in ascending order by the part number
the part number is listed more than once (each w/ diff location)

1st loc =
=IF(ISERROR(VLOOKUP($H4,$A:$E,3,0)),"",VLOOKUP($H4,$A:$E,3,0))
this looks at the whole database range from columns a:e and returns th
location based upon the 1st match for the lookup value -- no proble
here

2nd loc=
IF(ISERROR(VLOOKUP($H4,$A11:$E12,3,0)),"",VLOOKUP($H4,$A11:$E12,3,0))

I WANT THIS TO RETURN THE LOCATION FOR THE NEXT MATCH, WHICH MEANS I
THE 1ST LOOKUP VALUE WAS FOUND IN THE DATABASE TABLE IN A12 - I WANT M
LOOKUP RANGE TO BE A13:E?? FOR THE 2ND LOCATION IF THERE IS ON

 

[Dave Peterson]

There are a bunch of suggestions in this thread.

http://groups.google.com/[email protected]

I like the UDF <bg>.