VLOOKUP Value that is Repeated

[Thomas Price]

Hi I am so struggling today. I am doing a Vlookup on sheet 1 cell A1 which
contains the word "Hospital". On sheet2 I have a column with all of our
companies' jobs in column A. The problem I am running into is that we will
submit information several times on the Hospital job. So none of the entries
are complete but together between all 5 or 10 entries if you combine them you
get a complete entry. Is there a way to do a lookup and if the value in
sheet2 cell B1 is blank have it look for the next entry and if that one is
blank continue until it finds a value? Thanks in advance for your help!!!

 

[Dave]

Hi,
I think you say that of the multiple "Hospital" entries in Column A, only
one of them will have an entry in Column B.
If this is so, try:
=SUMPRODUCT(--(A3:A100=A1),(B3:B100))
You would have to insert the name of the other sheet (eg 'Sheet(1)!'A3:A100)
Regards - Dave

 

[T. Valko]

Is the value to be returned text or numeric?

 

[Thomas Price]

Depends on the Column. Some are numeric some are Text. Like address, phone
number, names.....

 

[Thomas Price]

Does this only work with Numeric answers? The answers are both.

 

[T. Valko]

Try this...

Returns the first non-blank cell that corresponds to the lookup value:

=INDEX(Sheet2!B1:B10,MATCH(1,INDEX((Sheet2!A1:A10=A1)*(Sheet2!B1:B10<>""),,1),0))