LOOKUP mayhem

[emoboyrulezdaworld2]

Hi,

I am a repair administrator for a company and i am seeking information
on how to make my life easier.

Agents forward concerns to me containing reference numbers and the
request itself. I then forward this information to our repair center
seeking updates.

i want to design an excel spreadsheet that would enable agents to input
the reference number, and a row will display the updates (if any are
available). This, i have already accomplished. However, i want to
lookup a value (input by user), and display all the instances of that
value (accompanied by to other colums as description). the values are
stored in a separate spreadsheet. im using VLOOKUP as a function, and
COUNTIF to count the number of instances.. problem is, I cant tell
excel to display the other values if the number of records found is
more than 1.

to display the first instance, I use: =VLOOKUP(C4,'[UPDATE
DB.xls]Sheet1'!$A$1:$C$300,1,) ... the request will be displayed by
using =VLOOKUP(C4,'[UPDATE DB.xls]Sheet1'!$A$1:$C$300,2,), and the
update would be displayed by =VLOOKUP(C4,'[UPDATE
DB.xls]Sheet1'!$A$1:$C$300,3,) ... This clearly makes excel diplay the
first instance of the reference number that was inputed in C4. I cant
display the other instances. (if C4 has three instances in the
database.)

i dont know if im making any sense here, but any help would be greatly
appreciated...

Thanks,
aj

 

[L. Howard Kittle]

Hi AJ,

Vlookup does not lookup multiple values, only goes to the first one ad the
game is over.

Perhaps you can use a helper column to the left of the current data and
combine the first two columns of the data and lookup that as a value and
return the 1st, 2nd and 3rd columns columns of the original data.

If you want, send me a sample workbook of your data and I will give it a go.

HTH
Regards,
Howard

 

[Max]

One set-up which could retrieve the multiple returns neatly ..

Sample construct available at:
http://cjoint.com/?lvhVeSBsyc
LookUp_MultipleReturns_emoboyrulezdaworld2_gen.xls

In sheet: Master,
Data is assumed in cols A to C, from row2 down

1112 Desc1 Text1
1113 Desc2 Text2
1112 Desc3 Text3
1114 Desc4 Text4
1113 Desc5 Text5
etc

[ "Master" will essentially be what you have
currently in cols A to C in your UPDATE DB.xls.
It's simpler to maintain within the same file.
You can always hide away the sheet. ]

Put in D1:
=IF(A1="","",IF(A1=Enquiry!$C$4,ROW(),""))

Copy D1 down to say, D20,
to cover the max expected extent of data

Then, in sheet: Enquiry

Assume the enquiry input (for the values in col A in Master: 1112, 1113,
etc)
will be made in cell C4. Input in C4, say: 1113

Put in D4:
=IF(ISERROR(SMALL(Master!$D:$D,ROW(A1))),"",
INDEX(Master!B:B,
MATCH(SMALL(Master!$D:$D,ROW(A1)),Master!$D:$D,0)))

Copy D4 across to E4, fill down to E23
(cover the same range size as was done in col D in Master)

The multiple results of the enquiry made in C4 will be displayed within
D4:E23, all neatly bunched at the top

Adapt to suit ..

 

[Max]

Typo, just detected:

In sheet: Master,
Data is assumed in cols A to C, from row2 down

2nd line above should read:

 

[comish4lif]

I've had to deal with this too...

What I ended up doing was doing a COUNTIF in the main sheet and
appending the counts to the original VLOOKUP target. For example, if I
was looking for "Apples" and there were 4 instances, the new column
would show: "Apples1", "Apples2", "Apples3" and "Apples4".

Now, the sheet that has the VLOOKUP you can add the COUNTIF to your
VLOOKUP value. This will give you something like this:

=vlookup("Apples"&countif(H26:H30)="Apples",A26:A30,1,FALSE) - where
column H is where you have appended the count to the value in column A.

Then in the cells below this, check
If(countif(A2:a30)-1=0,"",vlookup("Apples"&countif(H26:H30)-1="Apples",H26:H30,1,FALSE)).
This allows you to decrement the count as you paste down. Note I added
"-1" in the vlookup.