Home |
Search |
Today's Posts |
#1
|
|||
|
|||
a search function
i tried on couple search/lookup functions (or combination of few) but i still
could not produce the result i want... hope someone expert out there can help :) My sample data: A1:A3 value are fixed A1=James A2=Cole A3=Don B1:B3 value are variable (input area) where either is blank or with specific keyword like Yes or No. I would like to have a function to search through B1 to B3 for the keyword and output the corresponding A1:A3 value into a cell. Eg: if B1 and B3 contain Yes, then output A1 and A3 (James, Don) into a cell. B2 is ignored since it is blank. I tried like =match =vlookup =offset, I could only display the first result. Like example above, only James is displayed. I try not to use VBA (if possible) as I am not a good VBA programmer... Thanks in advance!! |
#2
|
|||
|
|||
if you only have a few rows try
=if(B1<"",A1&", ")&if(B2<"",A2&", ")&if(B3<"",A3&", ")&if(B4<"",A4) This will probably leave you with a "," at the end of your line. the formula can be made more difficult to remove it, or a second cell can be used with something like =if(right(C1,2)=", ", left(C1,len(C1)-2),C1 "jacko" wrote: i tried on couple search/lookup functions (or combination of few) but i still could not produce the result i want... hope someone expert out there can help :) My sample data: A1:A3 value are fixed A1=James A2=Cole A3=Don B1:B3 value are variable (input area) where either is blank or with specific keyword like Yes or No. I would like to have a function to search through B1 to B3 for the keyword and output the corresponding A1:A3 value into a cell. Eg: if B1 and B3 contain Yes, then output A1 and A3 (James, Don) into a cell. B2 is ignored since it is blank. I tried like =match =vlookup =offset, I could only display the first result. Like example above, only James is displayed. I try not to use VBA (if possible) as I am not a good VBA programmer... Thanks in advance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Improve the search function for help | Excel Discussion (Misc queries) | |||
FAQ Spreadsheet with search function | Excel Discussion (Misc queries) |