Vlookup possibly

**lian.cragg** · 03-17-2011, 10:47 AM

Hi,
Please see attached. On sheet 11 i have two columns, one part number and the other order number. On sheet 12 I would like to return the order number which still has stock, for the example would need to return line 23 column 4. If i do a standard vlookup it returns a blank, as the first vlookup is a blank cell.
:o(
Help!

**Roger Govier** · 03-17-2011, 11:04 AM

Hi

Try
=INDEX(Sheet12!$C$4:$C$42,MATCH(Sheet11!A4,Sheet12!$B$4:$B$42,0)*(Sheet12!$A$4:$A$42>0))

**lian.cragg** · 03-18-2011, 06:12 AM

Hi Roger, for some reason this worked fine in the example sheet but when i tried to apply the same formula to the live sheet is didn't work. If i used the exact formula you said it returned 0. If i changed the * to a , it only returns the first instance of the part number?
This is the formula im using in the live sheet, =IF(C1427="","",INDEX('HP CAs'!K:K,MATCH('Asset Delivery Sheet'!C1427,'HP CAs'!J:J,0),('HP CAs'!I:I>0)))
Iv tried using an array rather than columns, still just retuns the first value.
Any clues? :o)
Thank you.

**Roger Govier** · 03-18-2011, 07:17 AM

Hi Lian

Can't see any obvious reason.
You have to use the *. That is the equivalent of AND, which is what you want.
Other than your ranges being wrong, I can't immediately see what the problem is.
If you want to post your sheet up to the forum, so I can see the actual data, I might get a clue.

**tom.hogan** · 03-20-2011, 02:50 PM

Lian,
You can accomplish what you what with an array forumla. In cell B4 put the following:

=INDEX(Sheet12!$A$4:$D$42,MATCH(A4,IF(Sheet12!$D$4:$D$42<>"",Sheet12!$B$4:$B$42),0),4)

Now Push CTRL-SHIFT-ENTER (not just ENTER)

In B4 you will now see"

{=INDEX(Sheet12!$A$4:$D$42,MATCH(A4,IF(Sheet12!$D$4:$D$42<>"",Sheet12!$B$4:$B$42),0),4)}

The {} tells you it is an array formula, but you cannot just type them in, you have to enter the fromula with CTRL-SHIFT-ENTER. You can copy the formula down if there is any need to do so, it will stay as an array formula when you copy it.

FYI some folks refer to array formulas as CSE, for obvious reasons.

Regards,

Tom