LOOKUP mayhem

  • Thread starter emoboyrulezdaworld2
  • Start date
E

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

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
 
C

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.
 
Top