Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If / Vlookup Formula Help ¦!!
Hi there..,
Im experiencing a problem with a formula I am attempting to compile (nothing new there) Basically I require a formula to lookup some text within a specified cell (we shall say A1) and return the given result that identically matches the text throughout a large table (we shall say B1:G2500). The formula im using ¦.. =IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE))," ",VLOOKUP($A$1,$B$1: $G$2500,3,FALSE)) ¦. is returning all the correct results by searching column B for the specified text and in the above case is returning the result within the same row on column D (3). However the problem im finding is.., if column B doesnt contain the specified text its returning a duplicate result of the nearest cell that does contain the text (this is bad) and if I lose the appropriate $ symbols within my formula, it will happily return a completely blank row (also bad). Is it possible to amend this formula (or provide a new one) which would return the above results without duplicating or leaving a blank cell. Basically it would just move on to the next cell which contains the specified text (similar to applying a filter, which I wish not use if at all possible). Any thoughts.. many thanks, Monk |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If / Vlookup Formula Help ¦!!
Have you tried changing your optional False statement to true so the VLOOKUP
only looks for an exact match? -- Kevin Backmann "Monk" wrote: Hi there.., Im experiencing a problem with a formula I am attempting to compile (nothing new there) Basically I require a formula to lookup some text within a specified cell (we shall say A1) and return the given result that identically matches the text throughout a large table (we shall say B1:G2500). The formula im using ¦.. =IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE))," ",VLOOKUP($A$1,$B$1: $G$2500,3,FALSE)) ¦. is returning all the correct results by searching column B for the specified text and in the above case is returning the result within the same row on column D (3). However the problem im finding is.., if column B doesnt contain the specified text its returning a duplicate result of the nearest cell that does contain the text (this is bad) and if I lose the appropriate $ symbols within my formula, it will happily return a completely blank row (also bad). Is it possible to amend this formula (or provide a new one) which would return the above results without duplicating or leaving a blank cell. Basically it would just move on to the next cell which contains the specified text (similar to applying a filter, which I wish not use if at all possible). Any thoughts.. many thanks, Monk |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If / Vlookup Formula Help .!!
It's the other way around, Kevin, that's what puzzles me
-- Kind regards, Niek Otten "Kevin B" wrote in message ... Have you tried changing your optional False statement to true so the VLOOKUP only looks for an exact match? -- Kevin Backmann "Monk" wrote: Hi there.., I'm experiencing a problem with a formula I am attempting to compile (nothing new there) Basically I require a formula to lookup some text within a specified cell (we shall say 'A1') and return the given result that identically matches the text throughout a large table (we shall say 'B1:G2500'). The formula im using ... =IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE))," ",VLOOKUP($A$1,$B$1: $G$2500,3,FALSE)) .. is returning all the correct results by searching column B for the specified text and in the above case is returning the result within the same row on column D (3). However the problem im finding is.., if column B doesn't contain the specified text it's returning a duplicate result of the nearest cell that does contain the text (this is bad) and if I lose the appropriate '$' symbols within my formula, it will happily return a completely blank row (also bad). Is it possible to amend this formula (or provide a new one) which would return the above results without duplicating or leaving a blank cell. Basically it would just move on to the next cell which contains the specified text (similar to applying a filter, which I wish not use if at all possible). Any thoughts.. many thanks, Monk |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If / Vlookup Formula Help .!!
Hi Monk,
I'm with Niek, looks good to me. I would look at a sample workbook if you want to send me one. Regards, Howard "Monk" wrote in message ... Hi there.., I'm experiencing a problem with a formula I am attempting to compile (nothing new there) Basically I require a formula to lookup some text within a specified cell (we shall say 'A1') and return the given result that identically matches the text throughout a large table (we shall say 'B1:G2500'). The formula im using ... =IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE))," ",VLOOKUP($A$1,$B$1: $G$2500,3,FALSE)) .. is returning all the correct results by searching column B for the specified text and in the above case is returning the result within the same row on column D (3). However the problem im finding is.., if column B doesn't contain the specified text it's returning a duplicate result of the nearest cell that does contain the text (this is bad) and if I lose the appropriate '$' symbols within my formula, it will happily return a completely blank row (also bad). Is it possible to amend this formula (or provide a new one) which would return the above results without duplicating or leaving a blank cell. Basically it would just move on to the next cell which contains the specified text (similar to applying a filter, which I wish not use if at all possible). Any thoughts.. many thanks, Monk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & Dates: Why is this Formula working? | Excel Worksheet Functions | |||
VLOOKUP result is not showing up - only the formula | Excel Worksheet Functions | |||
IF / VLOOKUP formula won't work until saved | Excel Worksheet Functions | |||
What can I add to a vlookup formula to give me a 0 not #n/a | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions |