VLOOKUP or Alternative

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
Need some way ou

Sheet 2 Column A contains enteries against which value is taken in Column B through lookup (from sheet 1
But all enteries of Column A are not present in the lookup array; which is why I get #N/

In such cases (of the #N/A, I want the cell to take value of above cell (ex: B4=B3

Please Hel

Regard
Sachin
 
=IF(ISNA(vlookup_formula),B3,vlookup_formula)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Sachin said:
Hi!
Need some way out

Sheet 2 Column A contains enteries against which value is taken in Column
Click to expand...
B through lookup (from sheet 1)
 
You can test if the vlookup result is N/A, and based on that decide whether
to use the value of the cell above or the result of the vlookup, like this:
In B4 put
=IF(VLOOKUP(A4,table,col,FALSE)=#N/A,B3,VLOOKUP(A4,table,col,FALSE))
However this is mesy because the vlookup is being done twice. It would be
better to put the vlookup part into a different column, for example C, and
then in B4 put = IF(C4=#N/A,B3,C4).

Sachin said:
Hi!
Need some way out

Sheet 2 Column A contains enteries against which value is taken in Column
Click to expand...
B through lookup (from sheet 1)
 
Try this on row 4
=IF(ISNA(VLOOKUP(formula)),B3,VLOOKUP(formula))

HTH
Sachin said:
Hi!
Need some way out

Sheet 2 Column A contains enteries against which value is taken in Column
Click to expand...
B through lookup (from sheet 1)
 
Thanx a lo

----- Bob Phillips wrote: ----

=IF(ISNA(vlookup_formula),B3,vlookup_formula

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct
 
=IF(C4=#N/A,B3,C4
isn't working; it still returns #N/

Regard
Sachi

----- Stephen Bye wrote: ----

You can test if the vlookup result is N/A, and based on that decide whethe
to use the value of the cell above or the result of the vlookup, like this
In B4 pu
=IF(VLOOKUP(A4,table,col,FALSE)=#N/A,B3,VLOOKUP(A4,table,col,FALSE)
However this is mesy because the vlookup is being done twice. It would b
better to put the vlookup part into a different column, for example C, an
then in B4 put = IF(C4=#N/A,B3,C4).
 
Thanx Stephe

Regard
Sachi

----- Stephen Bye wrote: ----

Sorry, I should have tested it before posting
Use =IF(ISNA(C4),B3,C4) instead
 
Back
Top