#N/A error with Vlookup

[Khalil Handal]

Hi,
=VLOOKUP(BD16,$BN$5:$BQ$8,4,FALSE)

The range $BN$5:$bq$8 has values in colomns BN and BO.
when BD16 = value in BN colomn (any cell) then vlookup works fine,
when BD16 = value in colomn BO then #N/A error occurs.
Note: colomn BP does not have any values

Any ideas please!

 

[Gilgamesh]

Hi,
=VLOOKUP(BD16,$BN$5:$BQ$8,4,FALSE)

The range $BN$5:$bq$8 has values in colomns BN and BO.
when BD16 = value in BN colomn (any cell) then vlookup works fine,
when BD16 = value in colomn BO then #N/A error occurs.
Note: colomn BP does not have any values

Any ideas please!

VLookup works by looking up the value in only the first column of the range
you specify, so it is trying to find it in BN5 to BN8.
It sounds like you should be using the HLookup function instead.

 

[Pete_UK]

You could try it like this:

=IF(ISNA(MATCH(BD16,$BN$5:$BN$8,0)),IF(ISNA(MATCH(BD16,$BO$5:$BO
$8,0)),"",VLOOKUP(BD16,$BO$5:$BQ$8,3,0)),VLOOKUP(BD16,$BN$5:$BQ
$8,4,0))

It looks up using column BN first - if there is no match then it looks
up using column BO.

Hope this helps.

Pete

 

[Khalil Handal]

Will HLookup do for the first row only? If so, IT will be of no benefit! (I
guess???)
Can this be done with LOOKUP ?

 

[Khalil Handal]

It works fine, but it is two long since it will be part of a big formula...
Will LOOKUP do the job in a shorter form?

Thanks to both of you,

You could try it like this:

=IF(ISNA(MATCH(BD16,$BN$5:$BN$8,0)),IF(ISNA(MATCH(BD16,$BO$5:$BO
$8,0)),"",VLOOKUP(BD16,$BO$5:$BQ$8,3,0)),VLOOKUP(BD16,$BN$5:$BQ
$8,4,0))

It looks up using column BN first - if there is no match then it looks
up using column BO.

Hope this helps.

Pete

 

[Pete_UK]

From what I understand of your original query, you want to lookup
column BN and if there is no match then lookup column BO - that being
the case, you have to have two lookups, and each of these is repeated
through the error checking (although I've used MATCH here). If you
have XL2007 you can use IFERROR to shorten the formula slightly.

Having "big" formulae can slow down the performance of the workbook,
and can be difficult to maintain. Perhaps you could retain the formula
I gave you in one column and use the results in your "big" formula in
a different column.

Hope this helps.

Pete

 

[JP Ronse]

Hi Khalil,

Pete's formula avoids #N/A if the value in BD16 is not found in BN nor in
BO. If you are sure that the value exists in one of both ranges, you can
shorten Pete's formula this way:

=IF(ISNA(VLOOKUP(BD16,$BN$5:$BN$8,4,false),VLOOKUP(BD16,$BO$5:$BQ$8,3,false)),VLOOKUP(BD16,$BN$5:$BQ$8,4,false))Wkr,JP"Pete_UK" <[email protected]> wrote in messagewhat I understand of your original query, you want to lookupcolumn BN and if there is no match then lookup column BO - that beingthe case, you have to have two lookups, and each of these is repeatedthrough the error checking (although I've used MATCH here). If youhave XL2007 you can use IFERROR to shorten the formula slightly.Having "big" formulae can slow down the performance of the workbook,and can be difficult to maintain. Perhaps you could retain the formulaI gave you in one column and use the results in your "big" formula ina different column.Hope this helps.PeteOn Aug 11, 2:25 pm, "Khalil Handal" <[email protected]> wrote:> It works fine, but it is two long since it will be part of a bigformula...> Will LOOKUP do the job in a shorter form?>> Thanks to both of you,>> "Pete_UK" <[email protected]> wrote in message>> You could try it like this:>> =IF(ISNA(MATCH(BD16,$BN$5:$BN$8,0)),IF(ISNA(MATCH(BD16,$BO$5:$BO> $8,0)),"",VLOOKUP(BD16,$BO$5:$BQ$8,3,0)),VLOOKUP(BD16,$BN$5:$BQ> $8,4,0))>> It looks up using column BN first - if there is no match then it looks> up using column BO.>> Hope this helps.>> Pete>> On Aug 11, 10:08 am, "Khalil Handal" <[email protected]> wrote:>>>> > Hi,> > =VLOOKUP(BD16,$BN$5:$BQ$8,4,FALSE)>> > The range $BN$5:$bq$8 has values in colomns BN and BO.> > when BD16 = value in BN colomn (any cell) then vlookup works fine,> > when BD16 = value in colomn BO then #N/A error occurs.> > Note: colomn BP does not have any values>> > Any ideas please!- Hide quoted text ->> - Show quoted text -

 

[Khalil Handal]

Thanks for the shortening. I prefer it to avoid slowing down the
performance.
Thank to all of you. Every thing works well.