'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.50for each individual reference number!)

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

--

HTH

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


"Lisa" wrote in message
...
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.50for 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),"",VLO OKUP(A1,Sheet1!A1:B100,2,F
ALSE))

--

HTH

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


"Lisa" wrote in message
...
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.50for 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 ,
"Lisa" 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 ,
"Lisa" 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" wrote in message
...
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 ,
"Lisa" 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?