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!!!

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

Is the value to be returned text or numeric?

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

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

"T. Valko" wrote:

Is the value to be returned text or numeric?

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

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))

--
Biff
Microsoft Excel MVP


"Thomas Price" wrote in message
...
Depends on the Column. Some are numeric some are Text. Like address,
phone
number, names.....

"T. Valko" wrote:

Is the value to be returned text or numeric?

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

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

"Dave" wrote:

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