Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V and H look up
When Look up returns #N/A as there is no exact match in the range. Is it
possible to leave the cell blank? ie is there an "IF" "THEN" "ELSE" type function tha can be added to the look up formula? -- John from Bognor |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V and H look up
One way:
=IF(ISNA(MATCH(A1,J1:J100,FALSE)),"",VLOOKUP(A1,J1 :K100,2,FALSE)) In article , JR wrote: When Look up returns #N/A as there is no exact match in the range. Is it possible to leave the cell blank? ie is there an "IF" "THEN" "ELSE" type function tha can be added to the look up formula? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V and H look up
-- John from Bognor "JE McGimpsey" wrote: One way: =IF(ISNA(MATCH(A1,J1:J100,FALSE)),"",VLOOKUP(A1,J1 :K100,2,FALSE)) In article , JR wrote: When Look up returns #N/A as there is no exact match in the range. Is it possible to leave the cell blank? ie is there an "IF" "THEN" "ELSE" type function tha can be added to the look up formula? Thanks for the reply. The formula I currently have is a simple =HLOOKUP($K$1,B3:H3,1,FALSE) How would I re write this to match the formula suggested? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V and H look up
One way:
=IF(ISNA(MATCH($K$1,B3:H3,False)),"",$K$1) or, alternatively =IF(COUNTIF(B3:H3,$K$1),$K$1,"") Note that a HLOOKUP with an index row of 1 can only return one of two values: the value being looked up, or #N/A (assuming no errors in the range that get passed through). In article , JR wrote: Thanks for the reply. The formula I currently have is a simple =HLOOKUP($K$1,B3:H3,1,FALSE) How would I re write this to match the formula suggested? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
V and H look up
Many thanks JE McGimpsey. That works perfectly.
I owe you a beer or three -- John from Bognor "JE McGimpsey" wrote: One way: =IF(ISNA(MATCH($K$1,B3:H3,False)),"",$K$1) or, alternatively =IF(COUNTIF(B3:H3,$K$1),$K$1,"") Note that a HLOOKUP with an index row of 1 can only return one of two values: the value being looked up, or #N/A (assuming no errors in the range that get passed through). In article , JR wrote: Thanks for the reply. The formula I currently have is a simple =HLOOKUP($K$1,B3:H3,1,FALSE) How would I re write this to match the formula suggested? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|