Table Lookups

Posted by Mark on December 18, 2001 10:21 PM

I am wondering if there is a way to do this... I've been searching for a while and can't see it anywhere..

I have an UNSORTED table of lists (am doing the same thing on 12 different sheets, so don't want to have to sort them all)

using Vlookup I can get one value looked up..

Here's the problem,
Is there a way, if there are 2 values the same
example
1 - a
3 - b
2 - c
2 - d
4 - e

(using lookup value of 2) to get BOTH results, in 2 different cells? (in this case, one cell having "c" and other having "d") I won't need to get more than 2 results with this..

Posted by Aladin Akyurek on December 19, 2001 3:30 AM

If you want a quick way of manually checking, use Find & replace, replace it with ZZ099, and then replace it back again. The first time you do it should show you how many replacements were made.

Posted by Aladin Akyurek on December 19, 2001 11:30 AM

It seems my reply somehow caused the outage of this morning. I'm not superstitious, but who knows... fingers crossed and here we go...

Mark --

I'll assume that A2:B6 houses your sample data.

In C2 enter: 2 [ your lookup value ]

In D2 enter: =IF(COUNTIF($A$2:$A$6,C2),VLOOKUP(C2,$A$2:$B$6,2,0),"")

In E2 enter: =IF(COUNTIF($A$2:$A$6,C2)>1,VLOOKUP(C2,$B$6:INDIRECT("A"&MATCH(C2,$A$2:$A$6,0)+2),2,0),"")

Note. As you can see, 2 is added to the result of MATCH: 1 in order to skip the row that houses the first result value plus 1 (that makes 2) in order to take into account that the sample data starts in the second row. If the first value of the sample data would be in A5 (that is, row 5), the value to add would become 1+4 = 5.

Aladin

=========

Posted by Mark on December 19, 2001 12:50 PM

Thank you!

This was EXACTLY what I was after

Thank you!!

Mark