Home |
Search |
Today's Posts |
#1
|
|||
|
|||
'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!) |
#2
|
|||
|
|||
=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!) |
#3
|
|||
|
|||
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!) |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions | |||
SUMIF(AND) FUNCTION | Excel Worksheet Functions |