About Us

The Rimalaya

"T. Valko" wrote:

If your data table is sorted or grouped together as is shown in your sample:

A2:D10 = data table

F2 = lookup value = 34377007
G2 = instance number = 2

=INDEX(D2:D10,MATCH(F2,A2:A10,0)+G2-1)

Result = 5313312

--
Biff
Microsoft Excel MVP

"Alfonso Valdes" <Alfonso wrote in message
...
Hi I have a huge list of data that has items and each item has different
specifications. For example:

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200

What I want to do is use a vlookup(34377007,$A$1:$B$8,4,FALSE)
The output that this vlookup will give me would be "Z28031-1" but in some
cases I want the information of the second row"5313312" or maybe the
third"4758766".
I have seen that there is explanations, and formulas that give you all the
info like this:
34377007 Z28031-1
5313312
4758766
But for the purpose of what i am doing I do not need all the values I
just
need one of them.

for example:

Same vlookup, but I want the formula to give me the info from the second
row
when it found the first value that match the vlookup

I do not know if exist a formula that makes this
vlookup(34377007,$A$1:$B$8,4(row2),FALSE)
="5313312"

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200

Same vlookup, but I want the formula to give me the info from the third
row
when it found the first value that match the vlookup.
Vlookup (34377007, $A$1:$B$8, 4(row3),FALSE)
="4758766"

36134008 R AA Z34116-1
36153004 R AA Z33021-1
34377007 P AA Z28031-1
34377007 P ZZ 5313312
34377007 P ZZ 4758766
36321027 P AA Z00000-1
36321027 P AA Z30918-1
36416003 P AA X32118-1
36421026 P ZZ 2394200

Cann't we do the same thing, if the data are not sorted... ??

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Match and Lookup	Biocellguy	Excel Worksheet Functions	1	July 6th 07 01:48 AM
using MATCH, and LOOKUP	harvey1649	Excel Worksheet Functions	9	May 7th 07 07:05 AM
match, lookup ???	skimpw	Excel Worksheet Functions	0	August 14th 06 03:16 PM
Lookup? Match? pulling rows from one spreadsheet to match a text f	cjax	Excel Worksheet Functions	3	July 21st 06 02:51 PM
Lookup or Match	Robbyn	Excel Worksheet Functions	2	February 8th 05 10:29 PM

Menu

About Us