Lookup all occurrences

[nuver]

Hello
I need to lookup the value of an item number in a list that may b
listed several times but it will have no more than a single valu
listed in a column. Since VLookup will only return the first occurrenc
I get nothing returned if the first value for a particular item numbe
is blank for that particular column I am referencing. Can anyone pleas
point me in the right direction with the correct lookup function
should be using. Thank you for the help

 

[Ragdyer]

This old post should help answer your question.

http://tinyurl.com/4njwk

 

[Myrna Larson]

This is NOT a good layout to use with VLOOKUP. If the values in the body of
the table are numbers, you may be able to get this to work. I'll assume your
table is in A130, and that in each row only one of the columns has a value.

=MAX(INDEX($A$2:$D$30,MATCH(<your value>,$A$2:$A$30,0),0))

Another way would involve adding a helper column to your table, over on the
right. With the same layout as above, in E2 put the formula =MAX(B22) and
copy it down through row 30. Then use VLOOKUP this way:

=VLOOKUP(<the value>,$A$2:$E$30,5,0)

You can hide that column on the right, but be sure to put the formula in if
you add rows to the table.

If the information in the body of the table is not numeric, I don't see any
way except this last method, and the formula in the new helper column would
have to be something that gets the lone value into column E. For text (again
with only 1 value in a row), it could be =B2&C2&D2

If there's more than one value per row, you'll have to decide which one is to
be returned, and use a formula to get it into the right-hand column.

 

[Biff]

Hi!

Maybe something like this:

Entered as an array - CTRL,SHIFT,ENTER

=INDEX(K2:K20,MATCH(1,(J2:J20=A1)*(K2:K20<>""),0))

Where:

K2:K20 is the return range
J2:J20 is the lookup range
A1 is the lookup value

This is based on the assumption that of all the occurances
of the lookup value ONLY one will have a corresponding
return value as per your description.

Biff

 

[Myrna Larson]

I may have misread your message. I thought that for each row, there was a
value in ONE of the columns, and you wanted the value from that row,
regardless of the column it's in.

In re-reading, it looks like you said there's a value in only one of the rows
for that item, and you must retrieve it from a specific column.

I am assuming the values in the body of the table are numbers. If that's
correct and you want a value from column B, all you need is a SUMIF. With the
table in A230,

=SUMIF(A2:A30,<item number>,B2:B30)