Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup within an IF formula
I want to enter a function that in cell B15 will display an empty cell if A15
is blank but otherwise looks up information from a named range in another worksheet. I've got as far as clicking into B15 and putting in =if(A15,"","",=vlookup( The range is on a worksheet called "items" as follows: A3 B3 C3 D3 E3 F3 D001 Dijon Wall Unit Vanilla Gloss 300 £137.99 Jo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup within an IF formula
looks up information from a named range in another worksheet
Try something like this: =IF(A15="","",VLOOKUP(A15,named_range,column_index _number,0)) -- Biff Microsoft Excel MVP "Jo" wrote in message ... I want to enter a function that in cell B15 will display an empty cell if A15 is blank but otherwise looks up information from a named range in another worksheet. I've got as far as clicking into B15 and putting in =if(A15,"","",=vlookup( The range is on a worksheet called "items" as follows: A3 B3 C3 D3 E3 F3 D001 Dijon Wall Unit Vanilla Gloss 300 £137.99 Jo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup within an IF formula
Jo,
Remove the =vlookup and place VLOOKUP, also place the qualifier on the variable for your IF, where A15 = "". The IF format is: IF(<qualifying response,<if true,<if false) The VLOOKUP format is: VLOOKUP(<value,<range,<column number,<true/false) So: =if(A15="","",vlookup(.....)) -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Jo" wrote: I want to enter a function that in cell B15 will display an empty cell if A15 is blank but otherwise looks up information from a named range in another worksheet. I've got as far as clicking into B15 and putting in =if(A15,"","",=vlookup( The range is on a worksheet called "items" as follows: A3 B3 C3 D3 E3 F3 D001 Dijon Wall Unit Vanilla Gloss 300 £137.99 Jo |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup within an IF formula
You don't explain what data you want to return, so I'll assume that
you want all of it if there is a match. So, in B15 you can continue with your formula: =IF($A15="","",IF(ISNA(MATCH($A15,items!$A:$A, 0)),"absent",VLOOKUP($A15,items!$A:$F,COLUMN(B$1), 0))) then you can copy this formula into C15:F15 to return all the data associated with the code that you enter in A15. You can change "absent" to "" or some other message if you like. Hope this helps. Pete On Oct 8, 6:45*pm, Jo wrote: I want to enter a function that in cell B15 will display an empty cell if A15 is blank but otherwise looks up information from a named range in another worksheet. I've got as far as clicking into B15 and putting in =if(A15,"","",=vlookup( The range is on a worksheet called "items" as follows: A3 * * B3 * * * C3 * * * * * *D3 * * * * * * * *E3 * * *F3 D001 Dijon * * * * Wall Unit * * * Vanilla Gloss *300 * £137.99 Jo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula? | Excel Worksheet Functions | |||
Lookup Formula | Excel Discussion (Misc queries) | |||
Lookup formula | Excel Worksheet Functions | |||
Lookup formula? | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |