'IF' FUNCTION for 2 columns

I have a list of reference numbers in column A, and a list of prices in
column B (Sheet 1).
In a separate sheet (Sheet 2) I have ref numbers and their price next to
them (for different products).
Can I put a formula in Sheet 2 to automatically insert the price for the
refrence number (i.e. link Sheet 2 to sheet 1 without putting <=If ??=123456,
£2.50>for each individual reference number!)

=IF(ISNA(VLOOKUP(A1,Sheet1!A1:B100,2,FALSE),"",VLOOKUP(A1,Sheet1!A1:B100,2,F
ALSE))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lisa" <[email protected]> wrote in message
news:[email protected]...
> I have a list of reference numbers in column A, and a list of prices in
> column B (Sheet 1).
> In a separate sheet (Sheet 2) I have ref numbers and their price next to
> them (for different products).
> Can I put a formula in Sheet 2 to automatically insert the price for the
> refrence number (i.e. link Sheet 2 to sheet 1 without putting <=If
??=123456,
> £2.50>for each individual reference number!)

That doesn't seem to work - it;'s bringing up 'error' and highlighting the "
" section in the formula. I typed in:
=IF(ISNA(VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE),"
",VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE))

With C4 being the 1st reference number in column C and D3500 being the last
price refernce number...
Any suggestions?

"Bob Phillips" wrote:

> =IF(ISNA(VLOOKUP(A1,Sheet1!A1:B100,2,FALSE),"",VLOOKUP(A1,Sheet1!A1:B100,2,F
> ALSE))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lisa" <[email protected]> wrote in message
> news:[email protected]...
> > I have a list of reference numbers in column A, and a list of prices in
> > column B (Sheet 1).
> > In a separate sheet (Sheet 2) I have ref numbers and their price next to
> > them (for different products).
> > Can I put a formula in Sheet 2 to automatically insert the price for the
> > refrence number (i.e. link Sheet 2 to sheet 1 without putting <=If
> ??=123456,
> > £2.50>for each individual reference number!)
>
>
>

You're missing a paren. Try

=IF(ISNA(VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE)),
"", VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE))

In article <[email protected]>,
"Lisa" <[email protected]> wrote:

> That doesn't seem to work - it;'s bringing up 'error' and highlighting the "
> " section in the formula. I typed in:
> =IF(ISNA(VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE),"
> ",VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE))
>
> With C4 being the 1st reference number in column C and D3500 being the last
> price refernce number...
> Any suggestions?

Brilliant! Could someone just explain to me how it works? - I haven't used
Vlookup before, and what does ISNA stand for?
Thanks

"JE McGimpsey" wrote:

> You're missing a paren. Try
>
> =IF(ISNA(VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE)),
> "", VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE))
>
> In article <[email protected]>,
> "Lisa" <[email protected]> wrote:
>
> > That doesn't seem to work - it;'s bringing up 'error' and highlighting the "
> > " section in the formula. I typed in:
> > =IF(ISNA(VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE),"
> > ",VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE))
> >
> > With C4 being the 1st reference number in column C and D3500 being the last
> > price refernce number...
> > Any suggestions?
>

VLOOKUP returns #N/A if the value does not match. ISNA is just a function
that tests for #N/A.

So in logical terms the formula is saying

Do the VLOOKUP and if the result is no match (ISNA) then show "", else do
the VLOOKUP again and show the result.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lisa" <[email protected]> wrote in message
news:[email protected]...
> Brilliant! Could someone just explain to me how it works? - I haven't used
> Vlookup before, and what does ISNA stand for?
> Thanks
>
> "JE McGimpsey" wrote:
>
> > You're missing a paren. Try
> >
> > =IF(ISNA(VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE)),
> > "", VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE))
> >
> > In article <[email protected]>,
> > "Lisa" <[email protected]> wrote:
> >
> > > That doesn't seem to work - it;'s bringing up 'error' and highlighting
the "
> > > " section in the formula. I typed in:
> > > =IF(ISNA(VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE),"
> > > ",VLOOKUP(C4,'Other Committees schedules'!C4:D3500,2,FALSE))
> > >
> > > With C4 being the 1st reference number in column C and D3500 being the
last
> > > price refernce number...
> > > Any suggestions?
> >