vlookup with multiple occurence in column

Hi

First time post for me, so many thanks for any help at all that is forthcoming.

I use a vlookup that works fine if the looked up data appears only once in the looked up table, but gives me a problem if it occurs more than once.

The formula I use is VLOOKUP(B11,$AO$3:$BF$200,12,FALSE). B11 is the name of a horse and the looked up value in Row 12 is normally "Back" or "Lay". Sometimes though, the looked up cell is blank (intentionally), and in that case I would like to look up and display the cell in row 12 for the next occurrence of the horse's name in $AO$3:$BF$200.

Is this possible?

Best regards to all

Trickle.

Hi,

Your lookup range suggests that your lookup values are in column AZ, correct? This array formula (enter with CTRL+SHIFT+ENTER, not just ENTER) will find the first match ignoring blanks:

=INDEX($AZ$3:$AZ$200,MATCH(1,($AO$3:$AO$200=B11)*($AZ$3:$AZ$200<>""),0),1)

Regards

Hi Tony and XOR XL

Many thanks for such fast responses - both solutions work beautifully!

Best regards

Trickle.

Hi all

I actually spoke slightly too soon on this. The formula works well, except on a sheet where the lookup column (AZ) contains #N/A values, when it returns FALSE. If you have a solution to this I'd be very grateful.

Many thanks

Trickle

Can you post a sample illustrating this issue?

Hi XOR LX

Many thanks for getting back. I've attached a sample of the file.

The formula is in L9, L11, L13 etc. It should return either "Back", "Lay", or "", depending on what's in the relevant cell in column AX. Column AX contains lots of #N/A, because most the data in the table (AM3:BD199) references different races to the one displayed in columns B:K (the sheet is refreshed with a different race typically every five minutes).

The problem seems to be the #N/As in column AX (but I could be wrong!) Could the formula be amended to disregard them?

I really appreciate your time on this one.

Best regards

Trickle.

Hi Trickle,

You're aware that the formula which I gave you was set to look in the particular ranges as you originally specified and that, if for example your source columns or rows change then you may need to amend the ranges in your formulas?

You originally had your horses in column AO if I'm not mistaken. They appear to be in column AM in your new version. I presume also that the column of lookup values is no longer column AZ (perhaps AX?)

You should be aware also that your formulas as they currently stand are searching for the horse two rows beneath the row in which the formula stands. For example, the formula in cell L9 is searching for the horse in cell B11 - "Scatter Dice", and not the (correct, I presume ) "Harvard N Yale", and so on.

Your array formula in L9 should, I think, now be:

=IF($AI$31="Block","No More Bets",IF($J$5="Y",INDEX($AX$3:$AX$200,MATCH(1,($AM$3:$AM$200=B9)*($AX$3:$AX$200<>""),0),1)))

Hi XOR

Sorry, I meant to amend the references, which are slightly different to the original sheet - the look up values are, as you say, in AX. The original formula in L9, =IF($AI$31="Block","No More Bets",IF($J$5="Y",VLOOKUP(B9,$AM$3:$BD$200,12,FALSE),"")), correctly referenced B9, but had its limitations when there were multiple occurrences of the horse in AM. I've entered your last suggestion into L9, but it still returns FALSE.

Many thanks

Trickle

Doesn't for me. See attached. Just changed cell AI31 from "Block" to blank to check the lookup.

Many thanks, both. I've also got your solutions to work on my sheet on the laptop. I seem to get problems though (return of FALSE) when I try to apply the same formula to the live sheet on the remote server, although it's never given me that sort of problem before. I'll sort out what I'm doing wrong now you've pointed me in the right direction. I really appreciate your shared time and expertise.

Best regards

Trickle